Custom Sort Data

[Rajan_Verma]

Hi All,

 

Suppose I have 2 table in a Access DB, as showing below , 1^st one is Sort table where I define the sort order of data  and 2^nd one is Data table,

I wrote a query to sort the data as per my requirement , This query is working fine.

 

But is it not possible with having only one SELECT ?

 

Select A.Product From

(Select  Product From MainDB  Group by product)as A Inner Join Sort as B

On A.Product=B.Product

Order by B.SortNumber

 

Sort
SortNumber	Product
4	Product1
3	Product2
5	Product3
2	Product4
6	Product5
1	Product6

 

MainDB
Product	Pack	Date	Sales
Product6	Pack3	12-10-2012	111
Product4	Pack4	12-10-2012	149
Product1	Pack1	12-10-2012	179
Product3	Pack2	12-10-2012	161
Product2	Pack5	12-10-2012	153
Product1	Pack2	12-10-2012	140
Product1	Pack3	12-10-2012	175
Product2	Pack3	12-10-2012	155
Product6	Pack1	12-10-2012	188
Product5	Pack1	12-10-2012	123
Product6	Pack1	12-10-2012	132
Product4	Pack1	12-10-2012	108
Product3	Pack1	12-10-2012	121
Product3	Pack3	12-10-2012	197
Product2	Pack5	12-10-2012	136
Product5	Pack4	12-10-2012	119
Product1	Pack4	12-10-2012	125
Product4	Pack2	12-10-2012	183
Product3	Pack3	12-10-2012	177
Product2	Pack1	12-10-2012	113
Product4	Pack4	12-10-2012	159
Product2	Pack3	12-10-2012	107
Product4	Pack1	12-10-2012	193
Product6	Pack2	12-10-2012	150
Product2	Pack1	12-10-2012	177
Product3	Pack4	12-10-2012	188
Product6	Pack5	12-10-2012	100
Product6	Pack3	12-10-2012	150
Product5	Pack3	12-10-2012	168
Product5	Pack1	12-10-2012	161
Product2	Pack5	12-10-2012	186
Product4	Pack4	12-10-2012	112
Product1	Pack5	12-10-2012	109
Product1	Pack5	12-10-2012	170

 

 

 

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

 

[Lalit_Mohan]

May be this is what you are looking for:

Select A.Product From MainDB as A, Sort as B

Where A.Product=B.Product

Group By A.Product, B.SortNumber

Order by B.SortNumber

but i didn't understand the result because if you want this data then no need to join both the tables:

Select B.Product From Sort as B

Group By B.Product, B.SortNumber

Order by B.SortNumber

Only the above query will fetch the same.

Any way both are giving same result.

[Lalit Mohan Pandey]

Hi Rajan,

Find the database with all queries.

Regards,

Lalit Mohan

On Friday, October 12, 2012 11:28:03 PM UTC+5:30, rajan verma wrote:

[Rajan_Verma]

Thanks

I was looking for this ,

Can you explain how this is working internally ?

 

 

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]

 

--
Forum Rules:
1) Use concise, accurate thread titles. Don't use Poor thread titles, like Please Help, Urgent, Need Help, Query Problem, Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Acknowledge the responses you receive, good or bad.
4) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.
NOTE : Don't ever share your personal and confidential data . Forum owners and members are not responsible for any loss.
 
Group Email ID : acces...@googlegroups.com
Send Mail to Unsubscribe : access-sql+...@googlegroups.com
More Details about Group : http://groups.google.com/group/access-sql?hl=en