Query Help - 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Turing
Starting Member

3 Posts

Posted - 2007-09-20 : 17:22:04

Customer: customerId, customerName
Order: orderId,customerId,product, date, productDefinetion

I want to display the order which is the latest order from the customer and
I want to display all customer Name
Example:

customerName product productDefinetion
John Video bla bla bla
Maria ------ -----------

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 17:25:37

[code]
SELECT customerName, product, productDefinition
FROM YourTable y
INNER JOIN
(
SELECT customerName, MAX(date) AS date
FROM YourTable
GROUP BY customerName
) t
ON y.customerName = t.customerName AND y.date = t.date

[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Turing
Starting Member

3 Posts

Posted - 2007-09-20 : 17:52:00

In additon I want to displat the customer name which is not have any order in the same result

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 17:52:57

Well my query displays the customerName.

I don't know what the following means: "which is not have any order in the same result"

Please provide a data example of exactly what you have in your tables and then a data example of what result set should be returned.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Turing
Starting Member

3 Posts

Posted - 2007-09-21 : 07:03:10

my table is
Customer Customer Id
--------- ----------------
Mary 1
Jhon 2
Anna 3

OrderId CustomerId Product ProductDesc
------- --------- --------- -----------
1 1 video bla bla
2 1 tv bla bala
3 2 video bla bla
4 2 cd bla bla

I want to see
-------
marry tv bla bla
John cd bla bla
anna

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-09-21 : 08:39:50

Hi,

DECLARE @Customer TABLE ( Customer VARCHAR(30), CustomerId INT)

INSERT INTO @Customer
SELECT 'Mary', 1 UNION ALL
SELECT 'Jhon', 2 UNION ALL
SELECT 'Anna', 3

DECLARE @Order TABLE ( OrderId INT, CustomerId INT, Product VARCHAR(30), ProductDesc VARCHAR(30))

INSERT INTO @Order
SELECT 1, 1, 'video', 'bla bla' UNION ALL
SELECT 2, 1, 'tv', 'bla bala' UNION ALL
SELECT 3, 2, 'video', 'bla bla' UNION ALL
SELECT 4, 2, 'cd', 'bla bla'

--SELECT * FROM @Customer
--SELECT * FROM @Order

SELECT C.Customer, O.Product, O.ProductDesc
FROM @Customer C
LEFT JOIN ( SELECT CustomerId, Product, ProductDesc, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderId DESC) AS 'Rank' FROM @Order) O
ON O.CustomerId = C.CustomerId
AND O.Rank = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources