|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Use of SELECT...,0 FROMI am relatively new to SQL programming and I found some code that I am having
a problem understanding. The basic format of the code is a SELECT statement followed by some column references with the final being 0 FROM (i.e. SELECT [column ref], [column ref], 0 FROM... I googled this type of select statement and found numerous examples but there was no explanation as to what the zero was used for. Any enlightenment on this would be greatly appreciated. Thanks. -- Darius Sanders It just means that there's a column in your resultset that has its value
fixed to 0, rather than getting it from a column in the table(s). If you're curious, try something like: select getdate(), 1, 'abc', name from sys.objects --or sysobjects pre-SQL2005 .... you'll see that you get a few columns that give you the same result, and then only one from the table itself. Show quote "Darius Sanders" <DariusSand***@discussions.microsoft.com> wrote in message news:E07AFB5A-6C09-4586-9399-47C4D6FE9157@microsoft.com... >I am relatively new to SQL programming and I found some code that I am >having > a problem understanding. The basic format of the code is a SELECT > statement > followed by some column references with the final being 0 FROM (i.e. > SELECT > [column ref], [column ref], 0 FROM... > I googled this type of select statement and found numerous examples but > there was no explanation as to what the zero was used for. Any > enlightenment > on this would be greatly appreciated. Thanks. > -- > Darius Sanders Sometimes this is used in a UNION query to force ordering between the
queries that wouldn't normally be supported by the data itself. I can't think of a practical example right now, but consider: SELECT foo, x = 0 FROM bar1 UNION SELECT foo, x = 1 FROM bar2 ORDER BY x; This way, the rows from bar1 will be guaranteed to appear in the results "before" the rows from bar2. Show quote "Darius Sanders" <DariusSand***@discussions.microsoft.com> wrote in message news:E07AFB5A-6C09-4586-9399-47C4D6FE9157@microsoft.com... >I am relatively new to SQL programming and I found some code that I am >having > a problem understanding. The basic format of the code is a SELECT > statement > followed by some column references with the final being 0 FROM (i.e. > SELECT > [column ref], [column ref], 0 FROM... > I googled this type of select statement and found numerous examples but > there was no explanation as to what the zero was used for. Any > enlightenment > on this would be greatly appreciated. Thanks. > -- > Darius Sanders Here's a practical use:
3 different processes insert records into a table, and you want to be able to differentiate between the rows which were inserted by each one. So you have a query like: insert into mytable (cols...) select othercols... , 0 /* or 1 or 2 depending on the source */ from somewhere I know there are other ways of doing this, but this will be fairly effective. Any situation where the value is fixed in that context but you can't use a default value on the table itself will do it. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OEuTkUpiGHA.3588@TK2MSFTNGP02.phx.gbl... > Sometimes this is used in a UNION query to force ordering between the > queries that wouldn't normally be supported by the data itself. I can't > think of a practical example right now, but consider: > > > SELECT foo, x = 0 FROM bar1 > UNION > SELECT foo, x = 1 FROM bar2 > ORDER BY x; > > This way, the rows from bar1 will be guaranteed to appear in the results > "before" the rows from bar2. > > > > > > > "Darius Sanders" <DariusSand***@discussions.microsoft.com> wrote in > message news:E07AFB5A-6C09-4586-9399-47C4D6FE9157@microsoft.com... >>I am relatively new to SQL programming and I found some code that I am >>having >> a problem understanding. The basic format of the code is a SELECT >> statement >> followed by some column references with the final being 0 FROM (i.e. >> SELECT >> [column ref], [column ref], 0 FROM... >> I googled this type of select statement and found numerous examples but >> there was no explanation as to what the zero was used for. Any >> enlightenment >> on this would be greatly appreciated. Thanks. >> -- >> Darius Sanders > > You are just selecting a constant of value 0 as the last column for all the
rows in the result of this query.. I can give you an example why it may be used (though dumb it may look, it will solve the purpose :). Say you have two schematically similar tables tbl1 and tbl2. And you select rows from both tables and do a union on it, you may want to order the result set by rows of tbl1 and then by tbl2. then your query might look like this. select *, 0 as ord from tbl1 union all select *,1 as ord from tbl2 order by ord Do I make sense? Thanks guys, that makes sense.
-- Show quoteDarius Sanders "Omnibuzz" wrote: > You are just selecting a constant of value 0 as the last column for all the > rows in the result of this query.. > > I can give you an example why it may be used (though dumb it may look, it > will solve the purpose :). > > Say you have two schematically similar tables tbl1 and tbl2. And you select > rows from both tables and do a union on it, you may want to order the result > set by rows of tbl1 and then by tbl2. > then your query might look like this. > > select *, 0 as ord from tbl1 > union all > select *,1 as ord from tbl2 > order by ord > > Do I make sense? > > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > >  |
|||||||||||||||||||||||
Other interesting topics