CODEWITHSUNDEEP
sqlsql-servergroup-bydistinct
Sap1
Sap1

Reputation: 21

SQL "Distinct" and Group By Clause

searching around this site but does not see any similar and helping thread.

Example: I have a data as below

Col1       Col2
aaaa       1111
aaaa       2222
bbbb       4444
bbbb       3333

How can I query to have a table like below ?

Col1       Col2
aaaa       1111
           2222
bbbb       4444
           3333

My current query to test this purpose

SELECT Col1,Col2 FROM msaEPGerror 
FROM Table1
GROUP BY Col1,Col2

Tried using distinct and group by, but it still showing the other data as well. Yeah my db contained over hundred thousand of lines. Is it possible ? I'm just trying my luck since previously I design thing using Report Builder, but now trying to migrate it to SQL.

Thanks

Upvotes: 1

Views: 117

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

First of all: You don't want to use GROUP BY, because this is to aggregate your data, such as to have one result row only per col1.

Then you don't want to use DISTINCT either, because that eliminates duplicate rows. Your table doesn't contain duplicate rows.

What you really want to do is suppress repeated values when displaying your data. You have to choices here: Either do this in your GUI (in a programm you write or in a report engine), which would would be the most common way to do this. Or use SQL to look into the previous record. The function to do this is LAG.

select
  col1,
  case when t.col1 = lag(t.col1) over (order by t.col1, t.col2) then null else col1 end
   as col2
from table1 t
order by t.col1, t.col2;

Upvotes: 2

AT-2017
AT-2017

Reputation: 3149

Use the following query and it'll return a single value for a specific column:

SELECT CASE WHEN t.id = (SELECT TOP 1 t2.id FROM Table t2
WHERE t2.Col1 = t.Col1
GROUP BY t2.Col1) THEN t.Col1
ELSE
''
END AS Col1, t.Col2
FROM Table t

I guess, the table has a primary key.

Upvotes: 2

Gaurav Gupta
Gaurav Gupta

Reputation: 185

Using order by on col2 below query will work:

select case rank when 1 then col1 else '' end as col1, col2 
from (select col1,col2,
      rank() over (partition by col1 order by col2) as rank 
     from table1) tab;

Upvotes: 2

Related Questions