Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 3rd, 2005, 11:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 141
Thanks: 1
Thanked 0 Times in 0 Posts
Default SQL SubQuery

Hi All,

I have the following table;

Corp_ID Stacking Total
100 10 125
200 10 250
300 20 150
400 20 325

Based on these records setup, I would like to select the records with highest TOTAL
Within STACKING column.

I tried the following query, but it didn’t work.
SELECT *
    FROM #TABLE_1 AS t1
    WHERE t1.Corp_id not in
        (SELECT t3.Corp_id
            FROM (SELECT t2.Stacking, MAX(Total)
          FROM TABLE_1_t AS t2 GROUP BY Stacking) AS t3)


Thanks for your help,
:)


MCinar

Love all the creatures because of the creator.
__________________
MCinar

Love all the creatures because of the creator.
 
Old March 3rd, 2005, 11:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

maybe you can do something like:

SELECT * FROM <<table>> t1
INNER JOIN
(SELECT Stacking, MAX(Total) AS MaxTotal
FROM <<table>>
GROUP BY Stacking) AS t2
ON t1.Stacking = t2.Stacking AND t1.Total = t2.MaxTotal

but I'm not sure what you'll get if you have 2 or more records with the same Stacking and Total values.

hth
Phil
 
Old March 3rd, 2005, 12:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 141
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thank you.

MCinar

Love all the creatures because of the creator.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Subquery debbiecoates SQL Server 2000 4 June 25th, 2008 03:49 AM
Subquery as table brettk_1 Oracle 1 January 22nd, 2007 02:10 PM
Do i have to use a subquery, if yes then how? code_lover SQL Language 2 January 2nd, 2007 02:22 PM
subquery khansa MySQL 1 February 21st, 2006 02:49 AM
SQL Select problem (subquery needed??) deian SQL Language 7 February 3rd, 2004 02:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.