[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

URGENT min(), max() problem

Posted on 2005-05-09
7
Medium Priority
?
214 Views
Last Modified: 2010-03-19
Ok

min() and max()

I have a problem with a query which uses min or max and its driven me nuts!


SELECT
accuroNA.AddressID,
max(accuroNA.CustomerID) as ID,  
max(accuroNA.Title) AS title,
max(accuroNA.Initials) AS Initials,
max(accuroNA.surname) AS surname
FROM dbo.accuroNA
group by accuroNA.AddressID

is my query

AddressID  CustomerID  Title  Initial  Surname
51             82                Mrs  C        Benbow
51             84                Mr   S         Benbow
52             86                Mr   A         Benbow
52             88                Mrs E          Benbow

is my data

and my results are

AddressID  CustomerID  Title  Initial  Surname
51             84                Mrs   S        Benbow
52             88                Mrs   E         Benbow


as you can see its got the initial wrong for customer 84! How can I prevent that!? I know why, it seems to be sorting each column in the groupby clause.

Ideally what I would like is to just select the highest customerid and then get the whole row.

there are actually more columns in the table so I only actually want the 5 selected.

Andy
0
Comment
Question by:abenbow
  • 4
  • 3
7 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13962298
SELECT *
FROM dbo.accuroNA A
WHERE A.CustomerID = (SELECT MAX(CustomerID) FROM dbo.accuroNA B
                                    WHERE A.AddressID = B.AddressID)
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13962352
SELECT A.AddressID, A.CustomerID, A.Title, A.Initial, A.Surname
FROM dbo.accuroNA A
WHERE A.CustomerID = (SELECT MAX(CustomerID) FROM dbo.accuroNA B
                                    WHERE A.AddressID = B.AddressID)
0
 

Author Comment

by:abenbow
ID: 13962394
thank you!
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:abenbow
ID: 13969214
damn

ok this works brilliantly on my test data (40,000 records) but when I use it on my live data ( 100,000,000 records)  is goes realllllly slow. The query I posted is nice and fast, just wrong

any ideas??
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13969262
to make it faster, make sure you have an index on AddressID and CustomerID together in that order.
0
 

Author Comment

by:abenbow
ID: 13969347
already got that
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13969392
Given the number number of records and given that the indexes are in place, it is really expected for it to take long given the query that I provided.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question