CODEWITHSUNDEEP
sqlsql-server
Stack User
Stack User

Reputation: 1398

Getting one row sql

I have below table

Id          Stack
------------------
1             a
1             b
2             c
2             d

I want to get below table result.

Id          Stack
------------------
1             a
2             c

I use DISTINCT but it doesnot work how can I do it ? are there any solution by using group by?

Upvotes: 0

Views: 116

Answers (4)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36631

CREATE table sampletb(id int, stack varchar(10));

INSERT into sampletb values(1,'a');
INSERT into sampletb values(1,'b');
INSERT into sampletb values(2,'c');
INSERT into sampletb values(2,'d');

SELECT * FROM sampletb;

id          stack
----------- ----------
1           a
1           b
2           c
2           d


SELECT id,
       stack,
       row_number() over (partition by id order by stack) as row_number
from   sampletb;

This query will give you result like this.

id          stack      row_number
----------- ---------- --------------------
1           a          1
1           b          2
2           c          1
2           d          2

Now making the above query as subquery and getting the columns having row_number=1 will give the desire output you wanted.

select id,stack from 
       (
       SELECT id,
              stack,
              row_number() over (partition by id order by stack) as row_number 
       FROM   sampletb) x
where  x.row_number=1;

id          stack
----------- ----------
1           a
2           c

Upvotes: 2

Royi Namir
Royi Namir

Reputation: 148534

select id,stack from (
select id , stack  , row_number() over (partition by id order by stack) as rn
) k where rn=1

Upvotes: 1

Vytalyi
Vytalyi

Reputation: 1695

This should help

select Id, min(Stack) from <TABLE_NAME>
group by Id

Upvotes: 0

GarethD
GarethD

Reputation: 69769

I'm not sure of your exact logic that needs to be applied, but this would return the results you have posted:

SELECT  ID, MIN(Stack) AS Stack
FROM    T
GROUP BY ID

You can use more advanced logic with the ROW_NUMBER function if your DBMS allows it (none tagged in the question)

SELECT  ID, Stack
FROM    (   SELECT  ID,
                    Stack,
                    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Stack) AS RowNumber
            FROM    T
        ) t
WHERE   RowNumber = 1

Upvotes: 1

Related Questions