Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
 Posted - 2009-11-07 : 18:46:32
|
| Does anyone know if there is a rule using DENSE_RANK() having to do with where you can place columns in a select statement once you've used a DENSE_RANK() function in the query? Most of the time, I'm able to use DENSE_RANK() with no problem, but from time to time, I'll come up with a certain order of columns prior to the DENSE_RANK() portion and it will either return no results when results are expected, incorrect results or displays shifted columns. I also will come up with errors saying, "can not convert datatype x to datatype y" when no conversion was cast.EX:TableCol1 (int)col2 (varchar)col3 (int)COL4 (DATETIME)SELECT COL1, COL2, COL3, COL4 DENSE_RANK() OVER (PARTITION BY COL1 ORDER BY COL2, COL4) AS RANKFROM TABLEThe problem seems to come in when I try to move the columns around in the select statement. So for example, if I put COL4 between COL1 and COL3, it will say can not convert Datetime to int.Has anyone seen anything like this before?Thanks! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-11-07 : 19:50:34
|
Post some sample data to reproduce your question.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2009-11-07 : 20:25:50
|
| Hi Peso,I figured out what it was...It was my CTE, not the DENSE_RANK(). I didn't realize that when using a CTE,you couldn't use this format:;WITH MYCTE (COL1, COL2, COL3)AS (SELECT COL2 AS COL2, COL1 AS COL1, COL3 AS COL3 FROM MYTABLE) SELECT COL1, COL2, COL3FROM MYCTESo in the above example, the first column returned had the data of column 2.Thanks! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-11-08 : 02:07:07
|
Good you sorted it out.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2009-11-08 : 13:51:38
|
| Literally ;) thanks again |
|
|
|
|
|
|
|
|
DENSE_RANK()