Solved

SQL Query

Posted on 2015-01-06
17
180 Views
Last Modified: 2015-01-06
Hello,

I'm just having trouble with this simple query.  The following query is giving me two results.  I only want one.  It needs to find the ID number I specify and take the max POS_NR, then give me all the other fields in only that record.  What am I doing wrong?

Here's the query:

SELECT Max(SYSADM_BW_AUFTR_POS.POS_NR) AS MaxOfPOS_NR, SYSADM_BW_AUFTR_POS.PROD_BEZ1, SYSADM_BW_AUFTR_POS.PROD_BEZ2
FROM SYSADM_BW_AUFTR_POS
WHERE (((SYSADM_BW_AUFTR_POS.ID)=12200838))
GROUP BY SYSADM_BW_AUFTR_POS.PROD_BEZ1, SYSADM_BW_AUFTR_POS.PROD_BEZ2
ORDER BY Max(SYSADM_BW_AUFTR_POS.POS_NR);


And here's the result:

MaxOfPOS_NR      PROD_BEZ1                            PROD_BEZ2
1                            Dluminum Series Frames      Aluminum Covered Steel Profiles
2                            Aesigner Series Frames      Steel Profiles

Thanks!
Joel
0
Comment
Question by:Genius123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +2
17 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40534504
Use a subquery to get the max(POS_NR)

select *  From SYSADM_BW_AUFTR_POS
where pos_nr = (
                               select max(sysadm_BW_AUFTR.POS_NR)
                               FROM SYSADM_BW_AUFTR_POS
                               WHERE ID=12200838
                              )
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40534509

1.

Since there's only one table in your SQL Statement, lose all the table references other than in the FROM clause.  Doing that, and deleting the unnecessary parentheses marks, makes the query much easier to read.
SELECT Max(POS_NR) AS MaxOfPOS_NR, PROD_BEZ1, PROD_BEZ2
FROM SYSADM_BW_AUFTR_POS
WHERE ID = 12200838 
GROUP BY PROD_BEZ1, PROD_BEZ2

Open in new window

2.

Looks like we're grouping by two columns PROD_BEZ1 and PROD_BEZ2, so the return set will be all possible combinations of these two columns AND THEN the MaxOfPOS_NR
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40534515

3.

Then do the max in an in clause (or subquery, whatever floats your boat), and return the other values in the main query.
SELECT PROD_BEZ1, PROD_BEZ2, POS_NR as MaxOfPOSNbr
FROM SYSADM_BW_AUFTR_POS 
WHERE POS_NR IN (SELECT Max(POS_NR) FROM SYSADM_BW_AUFTR_POS WHERE ID = 12200838) 

Open in new window

0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 70

Expert Comment

by:Qlemo
ID: 40534539
That works only if the POS_NR is unique, so we need to change that approach if that is not the case.
0
 

Author Comment

by:Genius123
ID: 40534559
Sorry, neither one of those worked.  Let me simplify my example.

Two records.

Record 1
ID = 12200838
POS_NR = 1
PROD_BEZ1 = blah blah blah
PROD_BEZ2 = blah blah blah

Record 2
ID = 12200838
POS_NR = 2
PROD_BEZ1 = blah blah blah
PROD_BEZ2 = blah blah blah

I want to return Record 2 and all of the fields because it has the highest number is POS_NR.  ID and POS_NR are the unique identifiers.

Thanks.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40534561
SELECT TOP (1) sbap.POS_NR, sbap.PROD_BEZ1, sbap.PROD_BEZ2
FROM SYSADM_BW_AUFTR_POS sbap
WHERE (((sbap.ID)=12200838))
ORDER BY sbap.POS_NR DESC;
0
 

Author Comment

by:Genius123
ID: 40534575
What's sbap?  Never seen that before.  Also it gives me an error.  says .... misspelling, missing, or punctuation incorrect.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40534578
Give this a whirl, although I'm thinking it's the same as previous answers..

SELECT PROD_BEZ1, PROD_BEZ2, pos_max.MaxOfPOSNbr
FROM SYSADM_BW_AUFTR_POS pos
   JOIN (Max(POS_NR) as MaxOfPOSNbr FROM SYSADM_BW_AUFTR_POS WHERE ID = 12200838) pos_max ON pos.POS_NR = pos_max.MaxOfPOSNbr

Open in new window


Also, give us a sample data set of what we're querying here, as I'm guessing there are other things going on not in the question that may be impacting the results.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40534579
I'd say mine did work. See this SQL Fiddle example with your simplified query.

If this doesn't work for you, please confirm your SQL environment - is it MS SQL Server? Which version?

You wrote:
>ID and POS_NR are the unique identifiers.

But, you've used the same ID value for both records in your simplified example??

Edit: I included Scott's method in the SQL Fiddle example as well for good measure because I liked it.

As Jim suggests, it would help if you posted some samples of the actual datasets if these aren't working for you.
0
 

Author Comment

by:Genius123
ID: 40534604
Both still didn't work.  I'm running SQL from Access.  Maybe that's the problem?

Maybe saying unique identifier wasn't the right terminology.   Basically find the record where ID = 12200838 it's max POS_NR, then return the contents of that particular record.
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 250 total points
ID: 40534612
Are you querying a linked table or using a passthru query?

The very simple syntax I posted above and here should work in Access as well:
select *  From t1
where pos_nr = (
                               select max(POS_NR)
                               FROM t1
                               WHERE ID=12200838
                              ) 

Open in new window


Switch to the SQL view in the query builder and paste it in.

When you say they didn't work, can you be more specific? What results are you getting, or what error message?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40534617
>> What's sbap?  Never seen that before. <<

It's a table alias.  Better than having to type "SYSADM_BW_AUFTR_POS" over and over -- I tend to use the first letter of the table name segments as the alias, so s(YSADM_)b(W_)a(UFTR_)p(OS) = sbap :-).

If you are still on SQL 2000, try:

SELECT TOP 1 sbap.POS_NR, sbap.PROD_BEZ1, sbap.PROD_BEZ2
FROM SYSADM_BW_AUFTR_POS sbap
WHERE (((sbap.ID)=12200838))
ORDER BY sbap.POS_NR DESC

This is SQL Server and not DB2 or Oracle, right?
0
 

Author Comment

by:Genius123
ID: 40534629
Thanks for sticking with me on this.  Ok, but of the last two queries offered up do not produce errors, but when i run the query the hour glass pops up for a microsecond and nothing happens.  i'm running Access 2003 and it's linked to a SQL 2008 table.  i've run other queries on it - it's just this one i'm having issues with.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40534637
Picking up from Scott's last question: If you're generating SQL dynamically for a passthru query from Access, you need to use the particular SQL syntax of the server, whether that is Microsoft SQL Server, IBM DB2, Oracle, MySQL or whatever.

Edit: Just seen your latest post. If you're using linked tables, then it is Access SQL syntax you need, and most if not all of what has been posted above should work.

As it is only one linked table you're querying...
Can you open that linked table in datasheet view and copy a few rows of it to a spreadsheet and post that (after anonymising any sensitive data) please.
0
 

Author Comment

by:Genius123
ID: 40534647
Ok, I ran the query out of SQL Server Management Studio and it worked.  I think this should get me what I need. I need to run it from another software program to see if it works from there.  Standby.
0
 

Author Closing Comment

by:Genius123
ID: 40534669
Thank you both.  It works now.  That was a bit frustrating, but we got through it!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40534677
I know it's redundant in a sense, but I would include the ID condition in the other SQL just because it might help SQL with the query plan, depending on existing indexes:

select *  From t1
where pos_nr = (
                               select max(POS_NR)
                               FROM t1
                               WHERE ID=12200838
                              )
and ID=12200838

It certainly won't hurt anything.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 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