• 0

[SQL] - Simple easy SQL question (duplicate field)


Question

Hey,

I have an easy SQL question regarding rows with duplicate column fields. What I would like to do is return a maximum of 1 row if 1 or more are returned by select.

ClientID(PK) Firstname Lastname orderID(FK)

If there is a duplicate orderID, I'd like to return just one record of the typically two that return.

My SQL experience is pretty new, how would I return just one record matching my where clause?

Thanks

Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0

Only return one of the duplicates in a list of results, or only return one result?

The first would be,

SELECT *

FROM BLAH

GROUP BY ClientID, Firstname, Lastname, orderID

the other would be,

LIMIT 0, 1 (or just 1)

I think.

Thats for MySQL, it can be different with different dbs.

Link to comment
Share on other sites

  • 0

It's not a case of Microsoft being different, most of the major DBMS vendors implement this in different ways:

Oracle uses rownum, (e.g. SELECT * FROM (SELECT * FROM <table>) WHERE rownum <= 10),

DB2 uses fetch first (e.g. SELECT * FROM <table> FETCH FIRST 10 ROWS ONLY),

MySql/PostgreSql use limit (e.g. SELECT * FROM <table> LIMIT 10),

Informix uses first (e.g. SELECT FIRST 10 FROM <table>), and of course

Microsoft Sql and Access use top (e.g. SELECT TOP 10 FROM <table>).

Link to comment
Share on other sites

  • 0
It's not a case of Microsoft being different, most of the major DBMS vendors implement this in different ways:

Oracle uses rownum, (e.g. SELECT * FROM (SELECT * FROM <table>) WHERE rownum <= 10),

DB2 uses fetch first (e.g. SELECT * FROM <table> FETCH FIRST 10 ROWS ONLY),

MySql/PostgreSql use limit (e.g. SELECT * FROM <table> LIMIT 10),

Informix uses first (e.g. SELECT FIRST 10 FROM <table>), and of course

Microsoft Sql and Access use top (e.g. SELECT TOP 10 FROM <table>).

Ahh, I thought Oracle was Limit as well.

http://troels.arvin.dk/db/rdbms/#select-limit

Not sure if this site is accurate, but apparently Limit isn't part of the standard either. :(

Link to comment
Share on other sites

  • 0

This is probably a case of not being a part of the SQL specs, So each vendor implements it differently.

In this case, As described, TOP would do the trick.

Link to comment
Share on other sites

  • 0

MSSQL has a fantastic function called ROW_NUMBER

http://msdn.microsoft.com/en-us/library/ms186734.aspx

it's a lot more flexible then TOP.. e.g.

create table #temp (
	first varchar(10),
	second varchar(10),
	third varchar(10)
)

insert into #temp
select 'a','b','c'
union all
select 'a','b','d'
union all
select 'a','c','c'
union all
select 'a','b','c'
union all
select 'a','c','c'
union all
select 'a','b','a'

select
	row_number() over(partition by first,second,third order by first,second,third) as ROW_NUM,
	first
	,second
	,third
from #temp

results in:

ROW_NUM			  first	  second	 third
-------------------- ---------- ---------- ----------
1					a		  b		  a
1					a		  b		  c
2					a		  b		  c
1					a		  b		  d
1					a		  c		  c
2					a		  c		  c

so a simple where ROW_NUM = 1 would get you the results you are after... :)

You could also use count(*), group by, having etc... but its not as neat imo

Edited by BGM
Link to comment
Share on other sites

  • 0

I'm surprised I'm the only one asking this...

"And exactly why would you have duplicate order ID's ?!"

Something such as an order number would ALWAYS be unique. Either you would update the info regarding the order, delete the order, or create a new order. But NEVER have a duplicate.

[edit]

You probably meant more than one unique order ID is being returned, not duplicates. In this case, you would want all OPEN orders to come up. If you have a customer that ordered 20 items for pickup on Monday and ordered 10 items that are back ordered and won't arrive for 6 months, you may want to create two orders. However, on Monday, the customer will come in and your query would only bring up the 10 back ordered items.

Edited by Rohdekill
Link to comment
Share on other sites

  • 0
I'm surprised I'm the only one asking this...

"And exactly why would you have duplicate order ID's ?!"

Something such as an order number would ALWAYS be unique. Either you would update the info regarding the order, delete the order, or create a new order. But NEVER have a duplicate.

might not be an OLTP database...

could track the status of an order with different records, i.e. insert new record when changes from ordered -> picked -> shipped..

unlikely i know (but possible)

the other possibility is that he is attempting to rectify a mistake.. in which case the question is perfectly valid!

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.