help me with this select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

help me with this select statement

Author

Topic

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 21:09:39

Ok below is my sql query, what i want to is select the TableID = 2 but when the BillNo is maX


SELECT TableID=2, OrderID, Total
FROM Bill WHERE (BillNo =(SELECT MAX(BillNo)FROM  Bill))

Example

BillNo TableID OrderID Total
2 2 1 500
3 6 7 300
4 2 4 200 <--- i want to select this row

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 21:41:39

You are already selecting it via the WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 21:44:32

no the problem is, the result is false. It will display something else

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 21:46:51

I haven't a clue what you mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 21:49:21

Which part is it. I ll try to make it more clear

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 21:59:42

Here is why your problem is unclear. Run the following code to see that your query is already returning the row where BillNo is the maximum (BillNo = 4).


CREATE TABLE #Bill (BillNo tinyint, TableID tinyint, OrderID tinyint, Total smallint)

INSERT INTO #Bill VALUES(2,2,1,500)
INSERT INTO #Bill VALUES(3,6,7,300)
INSERT INTO #Bill VALUES(4,2,4,200)

SELECT TableID, OrderID, Total
FROM #Bill WHERE (BillNo =(SELECT MAX(BillNo)FROM  #Bill))

DROP TABLE #Bill

If your sample data doesn't illustrate your problem, then it is hard for us to help. So make it more clear with relevant data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:00:31

i want to select tableid, orderid and total, when the bill no is max. For example billno is 1 to 10 and tableid is 2(let say that in today bill all customer seat at table 2). So i want to retrieve the billno = 10 and tableid 2, because i want to know at table2, billno = 10, what is the total(price).

Instead this code only return billno = 10 but tableid = 2 and total(price) in the first row not the mac row which is 10

SELECT TableID=2, OrderID, Total
FROM Bill WHERE (BillNo =(SELECT MAX(BillNo)FROM Bill))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 22:03:30

I still don't understand. See my last post as it appears to do what you want. If not, show us with relevant sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:04:37

im sorry about the unclear explanation

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:12:50

Sample of DATA

BillNo___________TableID_________Total
_____1______________3______________200
_____2______________5______________100
_____3______________3______________666
_____4______________8______________333
_____5______________3______________555
_____6______________2______________444

Example of result that i want is

BillNo = 5, Tableid = 3 and Total = 555

IF still not clear, please do tell me. Maybe my english is bad

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 22:22:19

So you want the second highest BillNo? Or do you want the highest BillNo for a particular TableID?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:27:47

Yupp i want the highest BillNo for a particular TableID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 22:47:52

Here you go:


SELECT b.BillNo, b.TableID, OrderID, Total
FROM Bill b
INNER JOIN
(
	SELECT TableID, MAX(BillNo) AS BillNo
	FROM Bill
	WHERE TableID = 3
	GROUP BY TableID
) d
ON b.BillNo = d.BillNo AND b.TableID = d.TableID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 22:49:29

This should work too, but I prefer derived tables (above solution):


SELECT BillNo, TableID, OrderID, Total
FROM Bill
WHERE BillNo = (SELECT MAX(BillNo) AS BillNo FROM Bill WHERE TableID = 3) AND TableID = 3

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources