CODEWITHSUNDEEP
sqlsql-server
Alid Ek
Alid Ek

Reputation: 33

Difference query but same result

My query has to return : ID Order, amount of items in that Order, show just top one,.

First query i did by myself second is from tips. Both gave me same result.

So my ask is : My solution is wrong with that query or just show how iam beginner and made so bad things.

The think is I don't understand why i should to go for Join two tables in that query if i can call from just one table

Work on dataBase 'adventureWorks2012'

My query:

Select  Top 1
   SalesOrderID,
   COUNT(*) as Items
From 
   Sales.SalesOrderDetail
Group By 
   SalesOrderID
Order BY 
   Items DESC

Solution:

Select Top 1
   H.SalesOrderID,
   Count(*) as AmountItems
From 
   Sales.SalesOrderHeader As H JOIN SAles.SalesOrderDetail As D
      ON H.SalesOrderID = D.SalesOrderID
Group by 
   H.SalesOrderID
Order BY 
COUNT(*) DESC

Upvotes: 0

Views: 63

Answers (2)

LukStorms
LukStorms

Reputation: 29647

If SalesOrderDetail.SalesOrderID is a foreign key to SalesOrderHeader.SalesOrderID then the referential integrity is ensured. So in that case, it can be done without a join to SalesOrderHeader. And just group on SalesOrderDetail.SalesOrderID

SELECT TOP 1
 d.SalesOrderID, 
 COUNT(*) as AmountItems
FROM Sales.SalesOrderDetail d
GROUP BY d.SalesOrderID
ORDER BY COUNT(*) DESC;

But if SalesOrderDetail.SalesOrderID wouldn't be a foreign key? And you only want to count for SalesOrderID that actually exist in SalesOrderHeader? Then joining to SalesOrderHeader would assure that.

SELECT TOP 1
 d.SalesOrderID, 
 COUNT(*) as AmountItems
FROM Sales.SalesOrderDetail d
JOIN SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID
GROUP BY d.SalesOrderID
ORDER BY COUNT(*) DESC;

So assuming that SalesOrderID is the primary key for SalesOrderHeader. It is. And SalesOrderDetail.SalesOrderID is a foreign key to SalesOrderHeader.SalesOrderID. It is. Then your first query is a-ok.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query is fine. In fact, it is better than the other query, assuming that all values of SalesOrderID are in SalesOrderHeader. This is a very reasonable assumption (and it can be guaranteed if the a foreign key constraint has been defined).

You are also using an alias for the ORDER BY. That is another plus.

I think your query is simpler, faster, and shows a reasonable knowledge of SQL.

Upvotes: 1

Related Questions