SQL Helpline

« first day (4 days earlier) ← previous day next day → last day (640 days later) »

Malachi

22:40

@RubberDuck @Phrancis

Phrancis

@Malachi Yo

Malachi

I have a Category Column I want it Alphabetical

but I have a miscellaneous category that I probably want at the end, how can I do that in a query?

Phrancis

Misc category need to be sorted too, or?

OH nevermind

Hold on, easy stuff

Malachi

I am currently sorting on the description as well

the categories get turned into tabs and then the descriptions get listed under the tabs, but the code is already set up to read from the one query

Phrancis

SELECT
	Category,
	-- other stuff
FROM YourTable
WHERE Category <> 'Misc Category'
ORDER BY Category ASC

UNION

SELECT
	Category,
	-- other stuff
FROM YourTable
WHERE Category = 'Misc Category'

Malachi

22:44

lol

maybe not

Mehrad

SQL HELPLINE ?!?!

hahaha... never knew there is such a thing existed

Phrancis

You could do a UNION in your CTE, if need be and you need to do more complicated stuff

Mehrad

Hey @Phrancis. Now I know where you were hiding. :D

Phrancis

Bah, I'm only in 5 chat rooms pretty much all the time ;-)

Malachi

lol

Mehrad

22:46

Do you let @Phrancis-bot answer the question when you go to bed? :D

Sorry for the non-related entry though. :) Just thought I'll stop by and say hi.

Phrancis

@Mehrad You go to bed...?

Malachi

BLEH sticking to union.... I have never done it before!

Phrancis

Basically you can copy-paste your code before + after the UNION and just change the WHERE condition to include instead of exclude your special categories

UNION ALL is also faster, although that will leave duplicates in, if there are any

Malachi

it won't let me order by

Phrancis

o.O

Syntax error, or just not working?

Malachi

22:58

depending on where I put the first Order by, gives me either a Syntax error near UNION

incorrect syntax near 'order'. Expecting ')', UNION, or EXCEPT

maybe I should try without parenthesis?

still incorrect near union

Phrancis

Can you post a snippet (mostly what's just before and after UNION)?

Malachi

select uCode.CodeID as "EventID",
		uCode.Description as "EventName",
		uCaseEventType.BaseEventTypeKey as "BaseTypeKey",
		LTRIM(RTRIM(sEventType.Description)) as "BaseTypeDesc"
		, LTRIM(RTRIM(uCodeGroup.Description)) AS "EventGroup"
	from Justice.dbo.uCaseEventType AS uCaseEventType
       inner join Justice.dbo.uCode AS uCode on uCode.CodeID = uCaseEventType.CaseEventTypeID
       INNER JOIN Justice.dbo.xuEventTypeNodeGroup AS xuETNG ON uCaseEventType.CaseEventTypeID = xuETNG.CaseEventTypeID

some characters don't transfer well during copy/pasta

Phrancis

Try UNION ALL maybe?

Try removing parentheses in both of these:

	where (uCode.ObsoleteDate is null
			or uCode.ObsoleteDate > getdate())

Only other thing I can think of is try removing LTRIM and RTRIM on both the 'Miscellaneous' where clauses

Gotta go home, do let me know if you figure it out, I'd be curious. Never had any problems with UNION personally, as long as the column names and data types in the "SELECT" are the same...

RubberDuck

23:13

Wait. What's going on @Malachi?

Oh! How about something like this.

SELECT *,
    Case category
    When `Misc` Then 1
    Else 0
END as sortfield
FROM table
ORDER BY sortfield, category

Sorry for the awful formatting. On my mobile.

Actually, I don't think you even have to select the value. I think you can put the case in the order by.

Malachi

LET ME SEE HERE

oops

it doesn't like the Syntax for the Case Statement in the SELECT for some reason..... give me a second to figure out how to make this work with my query

Shortstuff81000

Q: Why don't SQL stored procedures parse image path strings?

I am creating a cheat/fan page for Doodle Creatures. I have gotten the page to work, but I am using inline SQL instead of stored procedures. I have tried making my Select statements stored procedures, but the image info doesn't get parsed correctly. Here is my SQL data source using the stored pr...