CODEWITHSUNDEEP
sqlteradata
user
user

Reputation: 2103

Reordering sql query by elements with most others associated

I have a table with two columns, A and B. To each element in A, there corresponds one or more elements in B. I want to re-order the table so that I have on top the elements in A to which there are associated the most elements in B.

Example:

Car     Blue
Home    Red
Home    Pink
Car     Yellow
Home    White
Bike    Blue

I want to output:

Home    Red
Home    Pink
Home    White
Car     Blue
Car     Yellow
Bike    Blue

What is the sql query I need to call to reorder the table this way? (This is in teradata, in case that made a difference). I need to add that to each element in A, there may be associated one or more rows with the same elemt B, I want the distinct count.

Example:

Car     Blue
Car     Blue
Home    Red
Home    Pink
Home    Pink
Car     Yellow
Home    White
Bike    Blue

Should return the same as above.

Upvotes: 1

Views: 64

Answers (4)

Andrew
Andrew

Reputation: 8703

Here's how I'd go about it:

SELECT
col1,
col2
FROM (
SELECT
col1,
col2,
1 AS rn
FROM
<your table>) t
GROUP BY 1,2
ORDER BY 1,SUM(rn) DESC

That lets us get a "count" of the number of rows for each col1/col2 intersection. It results in:

Bike    Blue
Car     Blue
Car     Yellow
Home    Pink
Home    Red
Home    White

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

You simply need to remove duplicates before the count. As GROUP BY all columns is the same as DISTINCT you can use this:

SELECT colA, colB
FROM tab
GROUP BY 1,2
ORDER BY Count(*) Over (PARTITION BY colA) DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You seem to want:

order by count(distinct b) over (partition by a) desc

However, I don't think this works in Teradata:

select t.*
from (select t.*,
             row_number() over (partition by a, b order by a) as seqnum
      from t
     ) t
order by sum(case when seqnum = 1 then 1 else 0 end) over (partition by a);

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Here is one trick using COUNT() OVER() windowed aggregate funtion 

select * from yourtable
Order by COUNT(1) OVER(PARTITION BY A) DESC

Upvotes: 1

Related Questions