[Fygar]

Ok, here's my problem, I want to make a query where it pulls records distinctly out of only one column, yet display all the others. Here's some example code:

SELECT DISTINCT Top 100 TicketNbr, DEX_ROW_ID FROM GP9SCLTD

And here is the Result:

TicketNbr DEX_ROW_ID  
--------- ----------- 
001003    266733
001003    266734
001003    266735
001003    266736
001119    263586
001119    263591
001119    263587
001119    263592
001119    263588
001119    263593
001119    263589
001119    263594
001119    263590
001119    263595
006515    291589
006515    291593
006515    291590
006515    291594
006515    291591
006515    291595
006515    291592
006515    291596

What I want it to do is sort distinctly by TicketNbr and leave DEX_ROW_ID be. so the top result would look like this:

TicketNbr DEX_ROW_ID  
--------- ----------- 
001003    266733
001119    263586
006515    291589

I NEEED to keep the DEX_ROW_ID in this for other purposes, and I can't have any duplicate TicketNbrs. Is there a way to do this in MsSql? MySql isn't an option in this.

[Cookie Mobster]

I don't understand what the Top 100 part of your code is?

[Fygar]

It's to recieve the top 100... lol, I just stuck that in there so I didn't recieve the other 170,000+ records. It is irrelevant to what I am asking

[Cookie Mobster]

Anyway I don't think there is anyway to do what your asking, It would be very intensive on the DBMS to pull the first file and the check for distintions while sorting the second field.

Cheers Sam

[Fygar]

Alright.... Thanks for the information, none-the-less.

[Garstor]

Fygar, on Jul 26 2002, 01:56 PM, said:

TicketNbr DEX_ROW_ID  
--------- ----------- 
001003    266733
001119    263586
006515    291589

I NEEED to keep the DEX_ROW_ID in this for other purposes, and I can't have any duplicate TicketNbrs. Is there a way to do this in MsSql? MySql isn't an option in this.

Is there some relation to the DEX_ROW_ID that you wish to preserve with your DISTINCT TicketNbr? At first glance I do not see a pattern. Getting the DISTINCT TicketNbr is the easy part. I suspect that a User-Defined Function (scalar return) can help you if you are coding this in MS-SQL2000.

Perhaps a stored procedure that utilizes either a temp table or the new (to MS-SQL2000) table variable to build the resultset you are looking for.

HTH!

-Garstor

[Fygar]

I worked around this a while ago, the DEX_ROW_ID's where all wacky or something, I really can't remember thanks though!