How to group rows with same value in sql? [closed]

How to group rows with same value in sql?

data1 123  12/03/2009
      124  15/09/2009
data2 333  02/09/2010
      323  02/11/2010
      673  02/09/2014
      444  05/01/2010

回答1:

Try this

DECLARE @temp TABLE(col1 varchar(20),col2 int, col3 varchar(20))
insert into @temp values ('data1', 123 , '12/03/2009'),('data1', 124 , '15/09/2009'),
                        ('data2 ',333  ,'02/09/2010'),('data2 ',323 , '02/11/2010'),
                        ('data2 ',673 , '02/09/2014'),('data2',444 , '05/01/2010')

SELECT 
    (CASE rno WHEN 1 THEN col1 ELSE '' END )AS col1,
    col2,
    col3
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY col2) AS rno,
        col1,col2,col3
    FROM @temp
) As temp

This gives the following output

col1    col2    col3
---------------------------------
data1   123 12/03/2009
        124 15/09/2009
data2   323 02/11/2010
        333 02/09/2010
        444 05/01/2010
        673 02/09/2014

PARTITION BY is grouping the data with the given column name, and a row number is generated in that group based on the order by.

Here is the SQL Fiddle

I have created another fiddle based on the schema provided .fiddle2

回答2:

I assume you have multiple same records as in below query. To select the distinct ones, you can either use GROUP BY or DISTINCT as below:

Using GROUP BY:

with datatab as
(
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  124 num,   '15/09/2009' datee from dual union all
select 'data2' dataa,  333 num,   '02/09/2009' datee from dual union all
select 'data2' dataa,  323 num,   '02/11/2010' datee from dual union all
select 'data2' dataa,  673 num,   '02/09/2014' datee from dual union all
select 'data2' dataa,  444 num,   '05/01/2010' datee from dual 

)
select dataa, num, datee from datatab 
group by dataa, num, datee
order by dataa;

USING DISTINCT:

with datatab as
(
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  123 num,   '12/03/2009' datee from dual union all
select 'data1' dataa,  124 num,   '15/09/2009' datee from dual union all
select 'data2' dataa,  333 num,   '02/09/2009' datee from dual union all
select 'data2' dataa,  323 num,   '02/11/2010' datee from dual union all
select 'data2' dataa,  673 num,   '02/09/2014' datee from dual union all
select 'data2' dataa,  444 num,   '05/01/2010' datee from dual 

)
select distinct dataa, num, datee from datatab 
order by dataa;

For both Queries, ORIGINAL DATA is :

dataa    | num   | datee
------------------------------
data1    | 123    | 12/03/2009
data1    | 123    | 12/03/2009
data1    | 123    | 12/03/2009
data1    | 124    | 15/09/2009
data2    | 333    | 02/09/2009
data2    | 323    | 02/11/2010
data2    | 673    | 02/09/2014
data2    | 444    | 05/01/2010

Query OUTPUT DATA is:

dataa    | num   | datee
------------------------------
data1    | 123    | 12/03/2009
data1    | 124    | 15/09/2009
data2    | 333    | 02/09/2009
data2    | 323    | 02/11/2010
data2    | 673    | 02/09/2014
data2    | 444    | 05/01/2010

EDIT: Refer Fiddle: http://sqlfiddle.com/#!3/4e3e80/9 created by Nithesh

This query would obtain the distinct records too:

SELECT 
(CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
*
FROM
(                   
SELECT 
    ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
    *
FROM (select distinct * from [endorsement]  ) a
) As temp1

Hope it helps.!

回答3:

You can use something like this:

Select col1, col2, col3 From tblName
Group By col1

Hope it helps you