select * from vwfillproducts where categoryid in('03-02','02-01','02-05')
i have this query when it executes it shows the records in grid vb.net 2005 as
02-01 first
02-05 second
03-02 third
but i need as
03-02 first
02-01 second
02-05 third
Since you didn't specify the Order By ... it's going to return the rows in the order in which they were encountered in the database. The actual order is going to be affected by indexes and other statistics.
Problem is, I don't see an easy way to sort order your data... if it's a static list, and doesn't change... then there is a way... but other than that....
hmmmm..... try this:
Code:
SELECT * FROM vwfillproducts WHERE CategoryID IN ('03-02','02-01','02-05') ORDER BY LEFT(CategoryID, 2) DESC, RIGHT(CategoryID, 2) ASC
thnx for reply
dear it's working only for this situation if we change the order it'll not work like.........
SELECT * FROM vwfillproducts WHERE CategoryID IN ('02-01','03-02','01-01') ORDER BY LEFT(CategoryID, 2) DESC, RIGHT(CategoryID, 2) ASC
or
SELECT * FROM vwfillproducts WHERE CategoryID IN ('03-02','02-01','04-01','01-01') ORDER BY LEFT(CategoryID, 2) DESC, RIGHT(CategoryID, 2) ASC
it's not working in such like situation i've number of records to show and i've try my best to do this but no success... plz give me some generic solution that it shows as in order i've given.
thnx in advance
Data type of categoryid is string/varchar and should be processed accordingly (sort as scending or descending string). Any other sort criteria on the column (all are workarounds to limitation of data architecture) will incur performance penalty as you will have to parse the string and these intermediate values are not indexed.
Another solution would be to re-architect the data (table) and store the digits separately to avoid parsing entirely.
Lastly, in post #5 you mentioned that ('03-02','02-01','04-01','01-01') did not sort properly but didn't CLARIFY with sample how it should have been sorted. I am under the impression that post #1 is misleading as it leads people to believe sort is based only on categoryid but how come the dilemma with new IN-list in post#5?
thnx for reply
i want to say that in post#5 the result of this query should be like this
SELECT * FROM vwfillproducts WHERE CategoryID IN ('02-01','03-02','01-01') ORDER BY LEFT(CategoryID, 2) DESC, RIGHT(CategoryID, 2) ASC
02-01 first
03-02 second
01-01 third
but it's not it's like this
03-02first
02-01second
01-01third
and if u we add one more record it's not changed........
thnx in advance..
thnx for reply
i want to say that in post#5 the result of this query should be like this
SELECT * FROM vwfillproducts WHERE CategoryID IN ('02-01','03-02','01-01') ORDER BY LEFT(CategoryID, 2) DESC, RIGHT(CategoryID, 2) ASC
02-01 first
03-02 second
01-01 third
but it's not it's like this
03-02first
02-01second
01-01third
and if u we add one more record it's not changed........
thnx in advance..
There's no logic to the sort sample you posted. Sort is limited to ascending and descending; try sorting only categoryid (no other basis for sort) in Excel and you'll see what I mean. It seems that you sort is based on criteria (other than categoryid) you did not elaborate on as you are only showing us the output and not explaining the logic.
dera frnd You r not understanding the problem i simply want to show records in order which i provide in my query. not ascending not descending.if you have any solution thn plz tell me abt that
dera frnd You r not understanding the problem i simply want to show records in order which i provide in my query. not ascending not descending.if you have any solution thn plz tell me abt that
thnx 4 replies
Again there is no way to sort data that way based solely on info from categoryid.
ok i agree with you that there's no way to sort out that way, tell me abt one thng i've two grids in vb.net 2005 in the first grid i select some model and on the base of that model it's items are loaded in the second grid.
i use "in" query there to load in second grid. if there's more than one models then there's no ordering in second grid.
i want that first time when i select suppose model 5 its products should be at first rows and then model2 products onward that based on in query.
can u tell me the solution..
So your sort is based on user activity, or is historical.
Use working table to populate second grid that can keep track of data per user session (other sessions see only their own data). Whenever items are selected in first grid, insert relevant records (selected from relevant source table and sorted per selection) to working table. All records in working table can be sorted on auto-sequence to ensure they are displayed FIFO. Clear working table accordingly, e.g. form load/close.
For now limitation to use of working table is only one open form per session/connection.
my dear frnd when u load first model items that's good but when u load more than on models then their products are arranged automatically because of there model no's so it cannt be as they are .........
thnx
my dear frnd when u load first model items that's good but when u load more than on models then their products are arranged automatically because of there model no's so it cannt be as they are .........
thnx
Second grid won't be bound directly to vwfillproducts... you will create another table wrk_vwfillproducts which is similar to vwfillproducts but with additional columns to keep track of sessions and to handle FIFO (first selected, first displayed until form is reset) sorting. Whenever user selects from first grid, an INSERT INTO wrk_vwfillproducts...SELECT...FROM vwfillproducts ORDER BY... is performed.
Unless you meant that whatever is selected in first grid, the child records in second grid are pushed up? Either way basis is user activity and not column values of vwfillproduct rows.
Last edited by leinad31; Apr 16th, 2010 at 02:22 AM.
At the expense of more code rather than an SQL based solution you can also use a disconnected grid instead (such as listview in VB6) and manipulate it progmaticaly (no need for working table in this case); write code to fill it up, code to code to sort or move contents/rows, and code to remove rows.
Help Required
Reply With Quote
Visual Studio 2008 Programming
Originally Posted by iactulip
