select only some rows and then the rest

Question

select only some rows and then the rest

Oracle765

SSCrazy

Points: 2862
More actions
August 11, 2013 at 10:51 pm

#278474

Hi Professionals.
I am stuck on a way around a query and wonder if it is possible.
I want to always select the first 3 rows in the exact order from my table and then the rest of the rows in now particular order. The table is created dynmaically so it will not always be the same columns in the first 3.
I have tried
select softwaremanufacturer,productname,productversion, * from newtable
but it shows those columns listed and then those columns listed again plus the rest like so
Microsoft CorporationOffice Access 200311.xMicrosoft CorporationOffice Access 200311.0.SP3 (jp)22/04/200530/04/200811.x
Microsoft CorporationOffice Access 200311.xMicrosoft CorporationOffice Access 2003NULL22/04/200530/04/200811.x
is there a way around this so it selects just the softwaremanufacturer,productname,productversion once
hope this makes sense
thanks in advance

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What do you mean by "in the exact order"? If the first three rows are nominated by a particular order then it would be more efficient to return the remaining rows in the same order than to randomise the order of them.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ohadpick Grasshopper Points: 23 More actions · Answer 2

If you upload your example, we could understand more.

Generally, you can always use IDENTITY column, or ROW_NUMBER() function.

Oracle765 SSCrazy Points: 2862 More actions · Answer 3

so it shows the softwaremanufacturer,productname,productversion then any other columns there may be in that particular order

I have tried

select softwaremanufacturer,productname,productversion, * from newtable

but it duplicates the softwaremanufacturer,productname,productversion because of using the *.

LinksUp SSCertifiable Points: 6526 More actions · Answer 4

Oracle765 (8/11/2013)
Hi Professionals.
I am stuck on a way around a query and wonder if it is possible.
I want to always select the first 3 [columns] ~~rows~~ in the exact order from my table and then the rest of the [columns] ~~rows~~ in no particular order. The table is created dynmaically so it will not always be the same columns in the first 3.
I have tried
select softwaremanufacturer,productname,productversion, * from newtable
but it shows those columns listed and then those columns listed again plus the rest like so
Microsoft CorporationOffice Access 200311.xMicrosoft CorporationOffice Access 200311.0.SP3 (jp)22/04/200530/04/200811.x
Microsoft CorporationOffice Access 200311.xMicrosoft CorporationOffice Access 2003NULL22/04/200530/04/200811.x
is there a way around this so it selects just the softwaremanufacturer,productname,productversion once
hope this makes sense

Did I make a correct assumption in that you were really talking about columns and not rows in your OP?

If so -- I understand that SQL does not guarantee the order of the columns when using SELECT *, but I would venture that SQL will return the columns in the order they are defined when the table is created.

SELECT * from NewTable

No duplicate columns. Problem solved!

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Oracle765 SSCrazy Points: 2862 More actions · Answer 5

yes i was talking about columns,

select softwaremanufacturer,productname and productversion are columns and not rows as there are more columns I am looking to select.

select * from newtable does not specify the order in which they are selected if the table structure is like so

softwaremanufacturer,edition, productexpirydate, productstartdate,productname,productversion,startdate,enddate

then they will come out in that order when I want them to be in the order

softwaremanufacturer,productname,productversion then the rest of the columns in the table

does that make more sense

LinksUp SSCertifiable Points: 6526 More actions · Answer 6

Oracle765 (8/11/2013)
The table is created dynmaically so it will not always be the same columns in the first 3.

If the table is created dynamically, can't you use a case statement, or something similar, to determine which 3 columns go first? Then you could use Select *.

Or, depending upon how the table is created and the type, temp, permanent, variable, you could use the following query to get the list of column names and build a select statement with it.

select syscolumns.name as [Column], syscolumns.xusertype as [User_Type], sysobjects.xtype as [Obj_Type]

from sysobjects

inner join syscolumns

on sysobjects.id = syscolumns.id

where sysobjects.xtype = 'u'

and sysobjects.name = 'YourTableName'

order by syscolumns.colid

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Colin Davidson Hall of Fame Points: 3756 More actions · Answer 7

Hi The result of the following can be run as direct sql for solution

select ' select softwaremanufacturer,productname,productversion'

union

select ' ,'+c.name

from syscolumns c inner join sysobjects o on c.id = o.id where o.name = 'newtable'

and c.name not in ('softwaremanufacturer','productname','productversion')

union

select ' from newtable'

Oracle765 SSCrazy Points: 2862 More actions · Answer 8

Hi SSCrazy

thanks for that

this now leaves me in the resultset with

(No column name)

,endeffectivedate

,starteffectivedate

from newtable

select softwaremanufacturer,productname,productversion

how do i finnaly concatenate this to say

select softwaremanufacturer,productname,productversion,endeffectivedate

,starteffectivedate

from newtable

thank you

Colin Davidson Hall of Fame Points: 3756 More actions · Answer 9

There are leading spaces in the select which are very important for the order in which the statements return

Oracle765 SSCrazy Points: 2862 More actions · Answer 10

Hi SSCrazy

yes i know but this still does not answer my question as to how to concatenate this together my query is showing

,endeffectivedate

,starteffectivedate

from newtable

select softwaremanufacturer,productname,productversion

which is invalid

it needs to be

select softwaremanufacturer,productname,productversion,endeffectivedate ,starteffectivedate

from newtable

thank you

Oracle765 SSCrazy Points: 2862 More actions · Answer 11

Hi SSCrazy

Yes i know but this still does not answer my question on how I actually get this in the right output format, at present it is showing

,endeffectivedate

,starteffectivedate

from newtable

select softwaremanufacturer,productname,productversion

which is invalid as i need

select softwaremanufacturer

,productname

,productversion

,endeffectivedate

,starteffectivedate

from newtable

thanks

Stefan_G SSCertifiable Points: 6609 More actions · Answer 12

You can use this:

use tempdb

go

if object_id('dbo.newtable') is not null drop table dbo.newtable

go

create table dbo.newtable (

endeffectivedate int,

starteffectivedate int,

softwaremanufacturer int,

productname int,

productversion int

)

go

declare @sql varchar(max)

select

@sql =

'select softwaremanufacturer,productname,productversion' + (

select ','+quotename(name)

from sys.columns c where c.object_id = object_id('dbo.newtable')

and name not in ('softwaremanufacturer','productname','productversion')

for xml path('')

) + ' from dbo.newtable'

select @sql -- Display the generated statement

exec(@sql) -- Execute the generated statement

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Something like this?

USE YourDatabaseName;

GO

CREATE TABLE newtable

(

softwaremanufacturer VARCHAR(100),

productname VARCHAR(100),

productversion VARCHAR(100),

a VARCHAR(100),

b VARCHAR(100),

c VARCHAR(100),

d VARCHAR(100)

);

GO

WITH cte

AS (

SELECT 'select softwaremanufacturer,productname,productversion' AS piece,

1 AS part

UNION ALL

SELECT ' ,' + c.name,

2 AS part

FROM sys.columns c

INNER JOIN sys.objects o ON c.object_id = o.object_id

WHERE o.name = 'newtable'

AND c.name NOT IN ('softwaremanufacturer', 'productname', 'productversion')

UNION ALL

SELECT 'from newtable',

3 AS part

)

SELECT piece

FROM cte

ORDER BY part;

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Colin Davidson Hall of Fame Points: 3756 More actions · Answer 14

Hi

I thought you would be able to work out the easy part

select ' select softwaremanufacturer,productname,productversion' as c1

union

select ' ,'+c.name as c1

from syscolumns c inner join sysobjects o on c.id = o.id where o.name = 'newtable'

and c.name not in ('softwaremanufacturer','productname','productversion')

union

select 'from newtable' as c1

order by c1

note 2 leading spaces before 1st select - 1 leading space before , and no leading spaces before from - this will put the select in the order you require.