CODEWITHSUNDEEP
sqlsql-server
user1357015
user1357015

Reputation: 11686

Select First Row from each group when many columns

Suppose I have a table as follows:

Name Part  Address City   ... Stuff 
Bob   X1     Y1      Z1   ... Stuff1
Bob   X2     Y1      Z1   ... Stuff1       
Bob   X3     Y1      Z1   ... Stuff1
Susan V1     Y2      Z2   ... Stuff2
Susan V2     Y2      Z2   ... Stuff2
....

Here, Stuff is many columns. Notice that Address, City, Stuff doesn't change.

So I just want to return the first row. I know I need to do something like

SELECT * FROM myTable
GROUP_BY (NAME)

but I'm not sure how to select the first row only after the group by? I saw other posts but they all were selecting based upon a min.. since my columns aren't numeric, I'm not sure how they would apply?

Upvotes: 0

Views: 75

Answers (3)

VISHMAY
VISHMAY

Reputation: 709

you can use top1 

     SELECT top(1) Name FROM myTable GROUP_BY (NAME),

in groupby, you cannot show column that is not in groupby with out using aggrigrate function

so you have option to do like

SELECT top(1) Name,Address, City FROM myTable GROUP_BY NAME,Address, City

or

SELECT top(1) Name,stuff((select ','+Address+city from myTable mt where mt.Name=myTable.Name for xmlpath('')),1,1,'') FROM myTable GROUP_BY NAME

Upvotes: -1

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

try this

 WITH CTE
    AS
    (
    SELECT ROW_NUMBER() OVER(PARTITION BY GroupColumn ORDER BY SomeColumn) as indx
        FROM YourTABLENAME
    )

    select * from CTE where indx=1

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use ROW_NUMBER instead.

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

SELECT *
FROM (
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Part)
    FROM tbl
) AS t
WHERE rn = 1

You can replace the ORDER BY column depending on your definition of first.

Upvotes: 4

Related Questions