General discussion

Locked

SQL Query

By vhansen86 ·
I am trying to query a database to to filter the database to list each product by the maximum line number and include other column info. Example database would be:

ID LineNo Prod Desc Price
100 12 Widgets Blah $10.99
100 13 Widgets Bong $12.99
100 14 Widgets Poof $11.99
101 12 Gadgets Blink $8.99
101 13 Gadgets Boof $7.99
101 16 Gadgets Bonk $6.99

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Query

by MadestroITSolutions In reply to SQL Query

Im not quite clear about what you want, but It might be this:

SELECT * FROM Product ORDER BY LineNo DESC

Hope that helps, and if not, feel free to drop a line

Collapse -

SQL Query

by vhansen86 In reply to SQL Query

Thanks for the suggestion. I clarified my need in "Comment".

Collapse -

SQL Query

by vhansen86 In reply to SQL Query

Please let me clarify further, I only want the most current\highest line number for each product listed. In the result set, there will only be one record for each product. The one with the highest line number. MAX only returns one record period. Or that was the only way I could get it to work.

Collapse -

SQL Query

by nikki96 In reply to SQL Query

General hint is to use the MAX function to determine the max LineNo and select only those that match. Look at GROUP BY, ORDER BY, and IN. HAVING may also work for you.

To get just one, you could do

SELECT * FROM myTbl
WHERE Prod LIKE '%Widgets%'
AND LineNo IN (SELECT MAX(LineNo) FROM myTbl WHERE Prod LIKE '%Widgets%')

That would only get one product at a time. I know there is a way to get them all with GROUP (where you wouldn't have to specify the Prod), but I'd need a DB set up to check my syntax.


Nikki

Collapse -

SQL Query

by Shanghai Sam In reply to SQL Query

Thanks for trying. I am trying to get more than just the one record. I need to have each product listed once with the highest LineNo. I have only been able to get the MAX to work as you have it working here. I have also tried the Group By, OrderBy, Having etc.

Collapse -

SQL Query

by kees.valkenswaard In reply to SQL Query

If you only want to have the Id, the maximum LineNo and the product name, something like this would do:

SELECT ThisTable.Id, Max(ThisTable.LineNo) AS MLineNo, ThisTable.Prod
FROM ThisTable
GROUP BY ThisTable.Id, ThisTable.Prod;

The main problem remains that you did not specify what else you want, the first Desc, the one on the line with the maximum line number etc.
If you are using Access, I would first make a query with the query wizard.
Just use the Sigma-button [like the auto sum in Excel], specify group by for Id and Prod, max for LineNo. I changed the name to MLineNo, thus MLineNo: LineNo and in the [third] total row the max.
Look next at the SQL and you see what I put above.
With the query builder it would be very easy to make a subsequent query based on this query with e.g. the name MAXline and ThisTable. Join the field Id in both and LineNo with MLineNo. Change the join type so that all records from MAXline will be shown and those of ThisTable which are identical.
If you would require the first of last name you can add the field to the query MAXline and no next query will be needed.
Good luck

Collapse -

SQL Query

by kees.valkenswaard In reply to SQL Query

Sorry, I said query wizard while meaning the query builder. There you can do what you want and check afterwards the SQL.

Collapse -

SQL Query

by Shanghai Sam In reply to SQL Query

I am using the SQL Query Analyzer. I am trying to get more than just the one record. I need to have each product listed once with the highest LineNo. Thanks for trying.

Collapse -

SQL Query

by timfox In reply to SQL Query

Group by product and ask for the MAX of whatever you want (ie LineNo), as follows:

SELECT First(p.ID) AS ID, Max(p.LineNo) AS Max_LineNo, p.Product, First(p.Description) AS [Desc], First(p.Price) AS Price
FROM tblProducts AS p
GROUP BY p.Product;

Expected output with given data:
-oOo-
ID, Max_LineNo, Product, Desc, Price

103, 16, Gadgets, Blink, $8.99
100, 14, Widgets, Blah, $10.99
-oOo-

Hope it helps
TimFox

Collapse -

SQL Query

by Shanghai Sam In reply to SQL Query

I tried this and errored on "First". Not recognized. I have tried and have not been successful to get the MAX function to work unless it entails all the columns in the select statement. Plus I am doing an Inner Join of two tables which could be throwing a wrench in. On the bright side, you are the only one who understood the output I am trying to get to! Thanks!
:-)

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums