| SQL - Questions and Answers Have a SQL question? Post it here. First do a search to see if someone hasn't already answered it. |
07-22-2008, 02:02 PM
|
|||
|
|||
ANY dialect question:
nested queriesI am using SQL Server 2005 don't which dialect that would be SQL 92 I am guessing !
How can I get: 1) only the Top 2 highest InvoiceTotal rows for each VENDOR in each STATE. 2) the VENDORNAMES that have supplied ONLY the highest number of Invoices for each state. For the first query, I know this is wrong but what should i be doing??
SELECT top 2 Invoicetotals, vendorname, vendorstateFor the second query, I didn't quite get it:
SELECT vendorname, vendorstate, count(*) AS InvoiceQtyHelp !     
|
|
07-23-2008, 05:59 AM
|
|||
|
|||
ANY answer.
Re: nested queries
this is the correct query for ur first question
select vendors.vendorname,vendors.vendorstate,invoices.in voicetotal from vendors join invoices on vendors.vendorid=invoices.vendorid where vendors.vendorid in(select top 2 vendorid from invoices order by invoicetotal DESC) Last edited by arun : 07-23-2008 at 11:35 AM.
|
|
07-23-2008, 06:51 AM
|
|||
|
|||
|
ANY answer.
Re: nested queries
i think this will be the answer for ur second question
create view new as select vendorid,"nova"=count(invoiceqty) from invoice group by vendorid select vendorname from vendors where vendorid in(select vendorid from new where nova=(select max(nova) from new)) Last edited by arun : 07-23-2008 at 11:36 AM.
|
|
07-23-2008, 11:41 AM
|
|||
|
|||
|
ANY answer.
Re: nested queries
when copy pasting the query check for syntax error coz query in edit page and the output differ
for example in the case of first query there is a space between in and voice of the word invoice which is not possible to edit from edit page. there is a chance of misunderstanding it as the keyword in. so check for such cases. those queries are found working in sql server 2005 Last edited by arun : 07-23-2008 at 11:43 AM.
|
