| Author |
Topic |
|
sanjay2988
Starting Member
16 Posts |
 Posted - 2009-01-07 : 02:09:29
|
| BillNo item 1 pepsi 1 coke 1 mirinda 1 lehar 1 fanta 1 thumbsup 2 fanta 2 coke 2 pepsi 2 mirinda 2 thumbsupabove is the table. i want to search for those BillNo in which buyer has purchased first pepsi -- coke -- than mirinda in a sequence. in BillNo 2 buyer purchased coke first than pepsi than mirinda. so sequence is not matching so i want a query which will return me BillNo 1 because in this transactions are in a sequence which i am searching For... Kindly help in making it.. very urgent.. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-07 : 02:18:56
|
| What column determines the sequence or items purchased?In SQL, there's no concept or row ordering. Without a column that defines the ordering, there's no way to say which row is first, second or last.--Gail ShawSQL Server MVP |
 |
|
|
sanjay2988
Starting Member
16 Posts |
Posted - 2009-01-07 : 02:22:53
|
| items inserted in table is the sequence.. like in this table pepsi purchased first then coke then mirinda.. when item is purchased it is inserted in table one by one.... |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-01-07 : 02:28:24
|
| can u post required output what u want |
|
|
|
sanjay2988
Starting Member
16 Posts |
Posted - 2009-01-07 : 02:31:28
|
| Yes if operator search for pepsi coke and mirinda it should give BillNo 1 as output. Because there is a sequence of these items. in Bill 2 coke comes before pepsi so this bill is not valid.in all output should be BillNo 1 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-07 : 03:18:16
|
| how many threads have you opened with same question? as suggested before, do you have any column in your table which helps you to identify sequence of entry of item? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-07 : 03:19:10
|
quote:
Originally posted by sanjay2988
items inserted in table is the sequence.. like in this table pepsi purchased first then coke then mirinda.. when item is purchased it is inserted in table one by one....
SQL doesn't have a concept of 'inserted order' or row position. Without a column to define the purchase order, there is absolutely no way to tell for sure which rows were inserted in which order.If you could add an identity column to the table it would resolve that problem, but as is, there's no way to consistently produce the results what you want. There are ways that will work sometimes, under specific conditions, but may change at any time.--Gail ShawSQL Server MVP |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-07 : 03:23:38
|
| or even an audit column like dateentered which captures the date record was created will serve the purpose. |
|
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-07 : 04:14:44
|
| declare @table1 table (billno int, item varchar(66))insert into @table1select t.billno,stuff((select ','+ item from @table where billno = t.billno for xml path('')),1,1,'') from urtable tselect distinct billno from @table1 where '%,'+item+',%' like'%,pepsi,coke,mirinda,%'Jai Krishna |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 03:05:35
|
quote:
Originally posted by Jai Krishna
declare @table1 table (billno int, item varchar(66))insert into @table1select t.billno,stuff((select ','+ item from @table where billno = t.billno for xml path('')),1,1,'') from urtable tselect distinct billno from @table1 where '%,'+item+',%' like'%,pepsi,coke,mirinda,%'Jai Krishna
how will this ensure if searched items are in correct sequence? what heppens if you have some other items coming between likepepsi,limca,coke,sprite,mirinda,...still this needs to be returned but it will not as per above query |
|
|
|
asafg
Starting Member
39 Posts |
Posted - 2009-01-08 : 03:54:17
|
| You need to add a new column itemIndexand change the key to order+itemIndex to create unique indexTABLE-Ordersorder | itemIndex | itemDetail_1 | itemDetail_2 | ... | itemDetail_nand then select min(itemIndex ) from OrdersWhere order = XGood luck |
|
|
|
|
simple sql query very urgent