Select All column with Distinct on two column

[Aditya jha]

Eg.

[U][B]Date       Team_num   Month   ORS_ID[/B][/U] 
2/1/2011   12203      FEB     122013
2/1/2011   13203      FEB     122013
2/1/2011   12203      FEB     151013
3/1/2011   11203      MAR     156003
3/1/2011   11203      MAR     147003
3/1/2011   18903      MAR     156003

I need the output as :

[U][B]Date       Team_num   Month   ORS_ID[/B][/U]
2/1/2011   12203      FEB     122013
2/1/2011   12203      FEB     151013
3/1/2011   11203      MAR     156003
3/1/2011   11203      MAR     147003

I want to select all column : Date, Team_num, Month, ORS_ID but with no repeat of same ORS_ID on same date
i.e. on 2/1/2011 only different ORS_ID.


Please help me.

[Rabbit]

How do you know which of the team numbers to pick?

[Aditya jha]

I hve to show all column with a particular date with unique ORS_ID. Team numbers will be picked according to date and ORS_ID

[Rabbit]

But in your example you have the same date and same ors_id but different team numbers. How do you know which one to pick?

[Aditya jha]

i need only in the below format

2/1/2011 12203 FEB 122013
2/1/2011 12203 FEB 151013
3/1/2011 11203 MAR 156003
3/1/2011 11203 MAR 147003

for 2/1/2011 there is two diff ors_ID 122013 & 151013
and 3/1/2011 there is also 2 diff ORS_ID 156003 & 147003

[Rabbit]

Yes, but there are two different team numbers, how do you know which one to pick.

You have

Date     Team_num Month ORS_ID 
2/1/2011 12203    FEB   122013
2/1/2011 13203    FEB   122013

How do you know which one to pick?

[Aditya jha]

i want to SELECT All from table but with date 2/1/2011 and UNique ORS_ID on a date. e.g. on 2/1/2011 there are three ORS_ID : 122013,122013,1 51013 out of which two is same and 1 is different, i need 122013 and 151013 as well as all column i.e. unique on a particular date...

[NeoPa]

If you look at the second record (#3) in your original data you will see it has Team_Num = 13203 and ORS_ID = 122013.

You have been asked now on four occasions to explain how it is that this record wasn't selected and the first record (#2) was (in your example output). If you continue to fail to explain this (IE. Answer this question) then we cannot help you. In short, until you explain that your question is nonsense. Not difficult. Nonsense. Impossible to answer.

Sort out what your question should have been (and answer this question) and we can start to help.

[Aditya jha]

Suppose i have three columns : like below
Date Team_num ORS_ID
2/1/2011 12203 122013
2/1/2011 12203 122013
2/1/2011 12203 151013
3/1/2011 11203 156003
3/1/2011 11203 147003
3/1/2011 11203 156003

I need output in the following manner:

2/1/2011 12203 122013
2/1/2011 12203 151013
3/1/2011 11203 156003
3/1/2011 11203 147003

I need all columns but different ORS_ID on a partiulcar date.

Please help me now...

[colintis]

Its simply a GROUP BY clause you want.

SELECT (the stuffs)
FROM Table_A
GROUP BY ORS_ID

The problem you have in your initial example is the records are already unique. SQL will not help you to decide taking out which team number (12203 or 13203), the solution to this is to remove the Team_num column. Otherwise this is a continuous nonsense.

[Aditya jha]

thanks

it works now....

[NeoPa]

@Aditya jha
You keep posting but you never answer the question. I made it very clear how impossible a sensible answer is without this answer (or making other unfounded assumptions). Why do you keep posting without answering?

Of course we could have given a half-answer right from the start, but that would be only mask the very real problem that you need to deal with first.