help me with a simple query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help me with a simple query

Author

Topic

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 19:31:39

Table Customer
CustomerID
CustomerName

Table Order
OrderID
CustomerID
OrderNo.

Is there a way to list all customers with only one (any one ) record of there order ?
Like:
CustomerA orderA
CustomerB orderB
Although customerA may have orderC, but I just want to list one of them.
Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:38:40

Have you tried using DISTINCT?

Tara

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 19:41:52

As far as know it won't work since the composition of customer and order no is unique. Is that right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:42:47

How about using SELECT TOP 1 then?

Tara

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 19:46:15

Can you give me an example, thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:47:34

SELEC TOP 1 *
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name

This will give you the top most record in sysobjects that is a table.

Tara

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 19:50:01

Can you please tell me how to join two table together with select top 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 19:58:03

quote:

Can you please tell me how to join two table together with select top 1

Sure...

SELECT TOP 1 o.name, c.id
FROM sysobjects o
INNER JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P' AND name NOT LIKE 'dt%'
ORDER BY name

Tara

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 19:58:48

Thanks very much, you are genius

Petehe
Starting Member

20 Posts

Posted - 2003-04-28 : 20:05:00

Sorry that didn't solve the problem
I want all the o.name and one c.id related to each o.name listed

Edited by - petehe on 04/28/2003 20:07:53

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-29 : 03:40:02

I thought we had done this enough times on this forum

Petehe:
When you say I want only one order per customer, will *any* order number do? Or do you have something more specific, like the last or first order? This should take care of something simple:

SELECT CustomerID, MAX(OrderID) AS LastOrder FROM [Order] GROUP BY CustomerID

As simple as that...and notice you dont need the Customer table, unless you have some customers who dont have any orders. In that case you need to do a left join to the Customer table, but the same trick applies.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources