asked on

properly sorting a Id_Column if varchar(50)

I'm trying to run a select statement but keep getting.
Id_Column      BrandMfrname
10        BATTALION
100        CONDOR
1000        PROTO
10000        3M
100000        3M
1000000        TIMBERLAND PRO
10000000      GE
10000001      WEILER
10000002      DEWALT
10000003      CONDOR
10000004      PROTO

The id column should be
1
2
3
4
etc....

SELECT [Id_Column]
,[Mfrname]
FROM [WeeklyCrsTextFile].[dbo].[descmatchGisXrefFuncExt]

where descmatchGisXrefFuncExt.[Id_Column] <= '1500000'

ORDER BY descmatchGisXrefFuncExt.[Id_Column]

This is not working..

Thanks
fordraiders

Jim Horn

>The id column should be 1 2 3
> where descmatchGisXrefFuncExt.[Id_Column] <= '1500000'

Tell us what the column data type of Id_Column is. If it's numeric (Id implies numeric) then something is wrong. If it's a character type column then the expression <= will be doing character comparison and not numeric comparison, which explains the return set.

Jim Horn

btw since your query only refers to one table, and none of the names require square brackets, it can be greatly simplified. Part of being an expert developer is writing T-SQL that is easily readable by others.

SELECT Id_Column, Mfrname
FROM WeeklyCrsTextFile.dbo.descmatchGisXrefFuncExt
WHERE Id_Column <= '1500000'
ORDER BY Id_Column

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Jim, understand the ID column data type... In inherited this table...cant reset it data types.

Jim Horn

Also please explain why the expected result set should contain 1, 2, 3, 4, ..., as I don't see those numbers in the source data mockup.

Fordraiders

ASKER

Jim, The result.
Id_Column      BrandMfrname
10      BATTALION
11      BATTALION
12      BATTALION
13      BATTALION
14      BATTALION
15      BATTALION
100      CONDOR

??

Jim Horn

Revisit your query and repost it in this question, as the only two explanations for the data set above is that there is a JOIN in the query that is returning multiple rows, or that's what's in your data.

Keep in mind that experts cannot connect to your data source(s) and run queries, so all we have to go off of is the explanations provided here.

Fordraiders

ASKER

jim, Thanks for the help...I just re-created the table with a int column identity seed.
re-imported the data.

let them to deal with it.

Thanks for the help.

dp

Jim Horn

Thanks for the grade. Good luck with your project. -Jim

ps Feel free to identify the original developer that chose to define an Id_Column as a varchar(50) and give them a swift smack upside the head. Or the ETL developer that chose to convert it to that.