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/
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 21:49:21
Which part is it. I ll try to make it more clear
Go to Top of Page

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/
Go to Top of Page

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))
Go to Top of Page

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/
Go to Top of Page

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:04:37
im sorry about the unclear explanation
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page

annas
Starting Member

36 Posts

Posted - 2008-04-09 : 22:27:47
Yupp i want the highest BillNo for a particular TableID
Go to Top of Page

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/
Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -