Prev: Selecting Top Records from Subquery
Next: Purple Indulgence——ghd new limited edition color collection
From: Glenn on 7 Jun 2010 17:21
I'm having difficulty with a query and hope someone can help me out.

Basically, I'm looking to select the maximum amounts from a table based on a
column in one of the tables named StateID.

It works fine when I do this:

SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount)
AS Amount, dbo.States.StateName
FROM dbo.Bids INNER JOIN
dbo.States ON dbo.Bids.StateID = dbo.States.StateID
GROUP BY dbo.Bids.StateID, dbo.States.StateName
ORDER BY dbo.States.StateName


However, when I start to join fields from other tables in the database, my
results then show multiple records for each StateID. (Instead of it just
showing the highest bid associated with the StateID, it shows all bids
associated with it.)

SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount) AS
Amount, dbo.States.StateName, dbo.Delegates.FullName, dbo.Bids.BidPlaced,

dbo.Bids.BidID, dbo.Delegates.Local

FROM dbo.Bids INNER JOIN

dbo.States ON dbo.Bids.StateID = dbo.States.StateID LEFT OUTER JOIN

dbo.Delegates ON Bids.DelegateID = dbo.Delegates.DeletageID LEFT OUTER JOIN

dbo.Delegates AS Delegates1 ON Bids.LocalID = Delegates1.DeletageID

GROUP BY dbo.Bids.StateID, dbo.States.StateName, dbo.Delegates.FullName,
dbo.Bids.BidPlaced, dbo.Bids.BidID, dbo.Delegates.Local

ORDER BY dbo.States.StateName



So, my question is how can I modify this query so that it'll only show the
highest amount bid for each particular StateID. I'm pretty sure that I need
to get the added fields out of "GROUP BY", but when I do that I get an error
stating

is invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause. I'm not sure how to make it part
of the aggregate function.



Thanks!


From: Tom Cooper on 7 Jun 2010 17:57
It's hard to know for sure what you want with the information you have given
us. Best for this type of question where you want a query that does some
particular task is to give us sample tables and data (in the form of
syntacally correct CTEATE TABLE and INSERT statements, please), a brief
description of what you are trying to do and the result you would want from
that sample data. But I think you probably want

WITH OrderedBids AS
(SELECT b.StateID, b.Amount, s.StateName, d.FullName, b.BidPlaced, b.BidID,
d.Local,
ROW_NUMBER() OVER(PARTITION BY b.StateID ORDER BY b.Amount DESC) AS rn
FROM dbo.Bids b
INNER JOIN dbo.States s ON b.StateID = s.StateID
LEFT OUTER JOIN dbo.Delegates d ON b.DelegateID = d.DeletageID)
SELECT o.StateID, o.Amount, o.StateName, o.FullName, o.BidPlaced, o.BidID,
o.Local
FROM OrderedBids o
WHERE o.rn = 1
ORDER BY o.StateName;

Tom

"Glenn" <nospam(a)yahoo.com> wrote in message
news:u9IYReoBLHA.4584(a)TK2MSFTNGP06.phx.gbl...
> I'm having difficulty with a query and hope someone can help me out.
>
> Basically, I'm looking to select the maximum amounts from a table based on
> a column in one of the tables named StateID.
>
> It works fine when I do this:
>
> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT
> dbo.Bids.Amount) AS Amount, dbo.States.StateName
> FROM dbo.Bids INNER JOIN
> dbo.States ON dbo.Bids.StateID = dbo.States.StateID
> GROUP BY dbo.Bids.StateID, dbo.States.StateName
> ORDER BY dbo.States.StateName
>
>
> However, when I start to join fields from other tables in the database, my
> results then show multiple records for each StateID. (Instead of it just
> showing the highest bid associated with the StateID, it shows all bids
> associated with it.)
>
> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount)
> AS Amount, dbo.States.StateName, dbo.Delegates.FullName,
> dbo.Bids.BidPlaced,
>
> dbo.Bids.BidID, dbo.Delegates.Local
>
> FROM dbo.Bids INNER JOIN
>
> dbo.States ON dbo.Bids.StateID = dbo.States.StateID LEFT OUTER JOIN
>
> dbo.Delegates ON Bids.DelegateID = dbo.Delegates.DeletageID LEFT OUTER
> JOIN
>
> dbo.Delegates AS Delegates1 ON Bids.LocalID = Delegates1.DeletageID
>
> GROUP BY dbo.Bids.StateID, dbo.States.StateName, dbo.Delegates.FullName,
> dbo.Bids.BidPlaced, dbo.Bids.BidID, dbo.Delegates.Local
>
> ORDER BY dbo.States.StateName
>
>
>
> So, my question is how can I modify this query so that it'll only show the
> highest amount bid for each particular StateID. I'm pretty sure that I
> need to get the added fields out of "GROUP BY", but when I do that I get
> an error stating
>
> is invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause. I'm not sure how to make it
> part of the aggregate function.
>
>
>
> Thanks!
>
>

From: Erland Sommarskog on 7 Jun 2010 18:02
Glenn (nospam(a)yahoo.com) writes:

> It works fine when I do this:
>
> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT >
dbo.Bids.Amount)
> AS Amount, dbo.States.StateName
> FROM dbo.Bids INNER JOIN
> dbo.States ON dbo.Bids.StateID = dbo.States.StateID
> GROUP BY dbo.Bids.StateID, dbo.States.StateName
> ORDER BY dbo.States.StateName

First of all, get that TOP (100) PERCENT out of the query, it serves
no purpose. (And if you are using the Query Designer, stop doing that.
It's a crappy tool.)


> However, when I start to join fields from other tables in the database, my
> results then show multiple records for each StateID. (Instead of it just
> showing the highest bid associated with the StateID, it shows all bids
> associated with it.)

Rather than using MAX, you need to use row_number to find the highest
amount, and all data that is associated with it.

WITH bidsnumbered AS (
SELECT StateID, Amount, BidPlace, DelegateID, LocalID, BidID,
rowno = row_number() OVER(PARTITION BY StateID
ORDER BY Amount DESC)
FROM dbo.Bids
)
SELECT b.StateID, b.Amount, s.StateName, d.Fullname, b.BidPlaced,
b.BidID, d.Local
FROM bidsnumbered b
JOIN dbo.States s ON b.StateID = s.StateID
LEFT JOIN dbo.Delegates d ON b.DelegateID = d.DeletageID
ORDER BY s.StateName

I've introduced aliases to make the query less verbose and more
readable. I also removed one instance of dbo.Delegates, because in
the query as written, it served no purpose.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Glenn on 7 Jun 2010 20:40
Thanks. I gave this a try, but it still gave me the same results - multiple
records for each stateID, not just the one that had the highest amount in
the "Amount" column. Am I missing something?

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D9176C8752Yazorman(a)127.0.0.1...
> Glenn (nospam(a)yahoo.com) writes:
>
>> It works fine when I do this:
>>
>> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT >
> dbo.Bids.Amount)
>> AS Amount, dbo.States.StateName
>> FROM dbo.Bids INNER JOIN
>> dbo.States ON dbo.Bids.StateID = dbo.States.StateID
>> GROUP BY dbo.Bids.StateID, dbo.States.StateName
>> ORDER BY dbo.States.StateName
>
> First of all, get that TOP (100) PERCENT out of the query, it serves
> no purpose. (And if you are using the Query Designer, stop doing that.
> It's a crappy tool.)
>
>
>> However, when I start to join fields from other tables in the database,
>> my
>> results then show multiple records for each StateID. (Instead of it just
>> showing the highest bid associated with the StateID, it shows all bids
>> associated with it.)
>
> Rather than using MAX, you need to use row_number to find the highest
> amount, and all data that is associated with it.
>
> WITH bidsnumbered AS (
> SELECT StateID, Amount, BidPlace, DelegateID, LocalID, BidID,
> rowno = row_number() OVER(PARTITION BY StateID
> ORDER BY Amount DESC)
> FROM dbo.Bids
> )
> SELECT b.StateID, b.Amount, s.StateName, d.Fullname, b.BidPlaced,
> b.BidID, d.Local
> FROM bidsnumbered b
> JOIN dbo.States s ON b.StateID = s.StateID
> LEFT JOIN dbo.Delegates d ON b.DelegateID = d.DeletageID
> ORDER BY s.StateName
>
> I've introduced aliases to make the query less verbose and more
> readable. I also removed one instance of dbo.Delegates, because in
> the query as written, it served no purpose.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Glenn on 7 Jun 2010 20:45
Sorry I wasn't more clear, but your query did the trick. Thank you!!!


"Tom Cooper" <tomcooper(a)comcast.net> wrote in message
news:O61v$xoBLHA.1888(a)TK2MSFTNGP05.phx.gbl...
> It's hard to know for sure what you want with the information you have
> given us. Best for this type of question where you want a query that does
> some particular task is to give us sample tables and data (in the form of
> syntacally correct CTEATE TABLE and INSERT statements, please), a brief
> description of what you are trying to do and the result you would want
> from that sample data. But I think you probably want
>
> WITH OrderedBids AS
> (SELECT b.StateID, b.Amount, s.StateName, d.FullName, b.BidPlaced,
> b.BidID, d.Local,
> ROW_NUMBER() OVER(PARTITION BY b.StateID ORDER BY b.Amount DESC) AS rn
> FROM dbo.Bids b
> INNER JOIN dbo.States s ON b.StateID = s.StateID
> LEFT OUTER JOIN dbo.Delegates d ON b.DelegateID = d.DeletageID)
> SELECT o.StateID, o.Amount, o.StateName, o.FullName, o.BidPlaced, o.BidID,
> o.Local
> FROM OrderedBids o
> WHERE o.rn = 1
> ORDER BY o.StateName;
>
> Tom
>
> "Glenn" <nospam(a)yahoo.com> wrote in message
> news:u9IYReoBLHA.4584(a)TK2MSFTNGP06.phx.gbl...
>> I'm having difficulty with a query and hope someone can help me out.
>>
>> Basically, I'm looking to select the maximum amounts from a table based
>> on a column in one of the tables named StateID.
>>
>> It works fine when I do this:
>>
>> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT
>> dbo.Bids.Amount) AS Amount, dbo.States.StateName
>> FROM dbo.Bids INNER JOIN
>> dbo.States ON dbo.Bids.StateID = dbo.States.StateID
>> GROUP BY dbo.Bids.StateID, dbo.States.StateName
>> ORDER BY dbo.States.StateName
>>
>>
>> However, when I start to join fields from other tables in the database,
>> my results then show multiple records for each StateID. (Instead of it
>> just showing the highest bid associated with the StateID, it shows all
>> bids associated with it.)
>>
>> SELECT TOP (100) PERCENT dbo.Bids.StateID, MAX(DISTINCT dbo.Bids.Amount)
>> AS Amount, dbo.States.StateName, dbo.Delegates.FullName,
>> dbo.Bids.BidPlaced,
>>
>> dbo.Bids.BidID, dbo.Delegates.Local
>>
>> FROM dbo.Bids INNER JOIN
>>
>> dbo.States ON dbo.Bids.StateID = dbo.States.StateID LEFT OUTER JOIN
>>
>> dbo.Delegates ON Bids.DelegateID = dbo.Delegates.DeletageID LEFT OUTER
>> JOIN
>>
>> dbo.Delegates AS Delegates1 ON Bids.LocalID = Delegates1.DeletageID
>>
>> GROUP BY dbo.Bids.StateID, dbo.States.StateName, dbo.Delegates.FullName,
>> dbo.Bids.BidPlaced, dbo.Bids.BidID, dbo.Delegates.Local
>>
>> ORDER BY dbo.States.StateName
>>
>>
>>
>> So, my question is how can I modify this query so that it'll only show
>> the highest amount bid for each particular StateID. I'm pretty sure that
>> I need to get the added fields out of "GROUP BY", but when I do that I
>> get an error stating
>>
>> is invalid in the select list because it is not contained in either an
>> aggregate function or the GROUP BY clause. I'm not sure how to make it
>> part of the aggregate function.
>>
>>
>>
>> Thanks!
>>
>>
>


 |  Next  |  Last
Pages: 1 2
Prev: Selecting Top Records from Subquery
Next: Purple Indulgence——ghd new limited edition color collection