6 Replies - 3203 Views - Last Post: 10 October 2002 - 09:34 AM

#1 Fygar  Icon User is offline

  • I liek milk!!1
  • member icon

Reputation: 6
  • View blog
  • Posts: 4,361
  • Joined: 28-March 02

'distinct' Function Alternatives

Posted 26 July 2002 - 12:56 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: 'distinct' Function Alternatives

#2 Cookie Mobster  Icon User is offline

  • nooneenooneenooonee
  • member icon

Reputation: 7
  • View blog
  • Posts: 4,730
  • Joined: 12-October 01

Re: 'distinct' Function Alternatives

Posted 26 July 2002 - 10:20 PM

I don't understand what the Top 100 part of your code is?
Was This Post Helpful? 0
  • +
  • -

#3 Fygar  Icon User is offline

  • I liek milk!!1
  • member icon

Reputation: 6
  • View blog
  • Posts: 4,361
  • Joined: 28-March 02

Re: 'distinct' Function Alternatives

Posted 27 July 2002 - 06:51 AM

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 :)
Was This Post Helpful? 0
  • +
  • -

#4 Cookie Mobster  Icon User is offline

  • nooneenooneenooonee
  • member icon

Reputation: 7
  • View blog
  • Posts: 4,730
  • Joined: 12-October 01

Re: 'distinct' Function Alternatives

Posted 27 July 2002 - 08:50 AM

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
Was This Post Helpful? 0
  • +
  • -

#5 Fygar  Icon User is offline

  • I liek milk!!1
  • member icon

Reputation: 6
  • View blog
  • Posts: 4,361
  • Joined: 28-March 02

Re: 'distinct' Function Alternatives

Posted 29 July 2002 - 05:29 AM

Alright.... Thanks for the information, none-the-less.
Was This Post Helpful? 0
  • +
  • -

#6 Garstor  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 25
  • Joined: 09-October 02

Re: 'distinct' Function Alternatives

Posted 09 October 2002 - 04:37 PM

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
Was This Post Helpful? 0
  • +
  • -

#7 Fygar  Icon User is offline

  • I liek milk!!1
  • member icon

Reputation: 6
  • View blog
  • Posts: 4,361
  • Joined: 28-March 02

Re: 'distinct' Function Alternatives

Posted 10 October 2002 - 09:34 AM

I worked around this a while ago, the DEX_ROW_ID's where all wacky or something, I really can't remember :) thanks though!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1