getting unique records from a table

Question

Post reply

getting unique records from a table

ganesh_sng

SSC Rookie

Points: 29
More actions
March 25, 2008 at 1:32 am

#184541

hi , can anyone can help me out...I am stuck in situation where
i want to retrive only the unique records from a table.
example :
roll sname
---- -------
1 aa
1 bb
1 cc
2 dd
3 ee
i want a query which will give
roll sname
---- -------
1 aa
2 dd
3 ee
Regards
Golu

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Probably very easy, but I do need a bit more info.

For Roll 1 you have sNames of 'aa', 'bb' and 'cc' Why is 'aa' the one you want returning, not bb or cc?

If you had something like this, what would you want returned?

roll sname

---- -------

1 Smith

1 Brown

1 White

2 Black

2 Jones

3 White

3 Smith

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ganesh_sng SSC Rookie Points: 29 More actions · Answer 2

hi,

thanx for ur response. actually i just want to retrive the unique records from that table no matter it will be aa or bb or cc i just want to retrive unique roll along with it's sname.

Koji Matsumura SSCrazy Points: 2221 More actions · Answer 3

Koji Matsumura

SSCrazy

Points: 2221

March 25, 2008 at 3:12 am

#793356

SELECT roll, MAX(sname) FROM Table ORDER BY roll

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Koji Matsumura (3/25/2008)
SELECT roll, MAX(sname) FROM Table ORDER BY roll

SELECT roll, MAX(sname)

FROM Table

GROUP BY roll

ORDER BY roll

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koji Matsumura SSCrazy Points: 2221 More actions · Answer 5

GilaMonster (3/25/2008)
Koji Matsumura (3/25/2008)
SELECT roll, MAX(sname) FROM Table ORDER BY roll
SELECT roll, MAX(sname)
FROM Table
GROUP BY roll
ORDER BY roll

Thank you Gail.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

I've never understood this type of request... and it's a fairly frequent request. If the SName (in this case) doesn't matter, why bother? Not trying to be a smart guy about this... I just don't understand what the real purpose here is. Can anyone explain this with something other than "it doesn't matter what we return, we just need one"? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Saravanakumar.Rathinam Ten Centuries Points: 1365 More actions · Answer 7

I hope, Required the information like the method of foxpro.

select CardNo,block,crlim from tablea group by CardNo

cardno block crlim DateOpened

101 NULL 10000 10/01/2004

101 L 10000 10/01/2000

'L' denotes card has lost and the customer got a new card.

In foxpro it works and it returns the first row. I hope, they want a similar query in SQL.

Regards

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

I appreciate that very much. I'm thinking that's not quite the same because the OP said they didn't care which row was returned... you would think in the example you gave that the row with "NULL" for BLOCK or at least the latest date would be the row they want in that case. The OP said it didn't matter which row was returned...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Ganesh,

Can you help me out, please? Why don't you care what the SName for a given roll is so long as you return one? Like I said... I'm not trying to be a smart guy about this... I really wanna know because I've never been able to get anyone to explain to me why they need to return data that it looks like they don't really care about. Thanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 10

We got into just this sort of thing converting some legacy data.

The old system had tickets, items, and fees. The items were linked to the tickets, the fees were linked to the ticket, but the fee wasn't linked to a specific item. In the new and improved system, it is.

When we imported the legacy data, I needed to link the fee to an item, and since there was no way to figure out which one, we linked all the fees to the first item on the ticket.

Probably not what he is doing, but I had a real need to just pick one from a set.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

See? Now, I get that... thanks, Wayne. Why couldn't someone say something like that instead of thinking that I was trying to give them an eWedgie? 😛 I really appreciate the time you took for that response.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions