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 maXSELECT TableID=2, OrderID, TotalFROM Bill WHERE (BillNo =(SELECT MAX(BillNo)FROM Bill)) ExampleBillNo 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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, TotalFROM #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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 10SELECT TableID=2, OrderID, TotalFROM 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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 DATABillNo___________TableID_________Total_____1______________3______________200_____2______________5______________100_____3______________3______________666_____4______________8______________333_____5______________3______________555_____6______________2______________444Example of result that i want isBillNo = 5, Tableid = 3 and Total = 555IF 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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, TotalFROM Bill bINNER JOIN( SELECT TableID, MAX(BillNo) AS BillNo FROM Bill WHERE TableID = 3 GROUP BY TableID) dON b.BillNo = d.BillNo AND b.TableID = d.TableID Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://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, TotalFROM BillWHERE BillNo = (SELECT MAX(BillNo) AS BillNo FROM Bill WHERE TableID = 3) AND TableID = 3 Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|