Select First N Rows Of Table

Mar 28, 2006

Let us say that I have a table with two sets of values as such:

Item Extension
--- ----
100023 1
100025 1
100025 2
100028 1
100029 1
100029 2
100029 3
[...]

Note that a given item number can appear multiple times if it has more
than one extension number.

I want to be able to select the first N entries as grouped by item
number. So if N = 3, that would return 100023, 100025 and 10028 with
their associated extentions. I would also like to be able to select
say, the 2nd through 9th entries grouped by item number.

I've tried something like this to give me row counts:

select rank = count(1), t1.item, t1.extension
from ItemTable t1 inner join itemTable t2
on t1.item >= t2.item
group by t1.item, t1.extension
order by rank

But that gives me this sort of result:

Rank Item Extension
---- --- ----
1 100023 1
3 100025 1
3 100025 2
4 100028 1
7 100029 1
7 100029 2
7 100029 3
[...]


Any suggestions would be welcome.

View 2 Replies


ADVERTISEMENT

HOW TO SELECT ROWS IN THE MASTER TABLE WITH NO RELATIONED DATA IN DETAILS TABLE

Dec 7, 2007

I have the following data

MASTER
id
name


DETAIL
id
master_id
name

I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

Select ROWS In Table A That Aren't In Table B

Jan 28, 2008

I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.

I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).

Suppliers_To_Categories contains which suppliers are related to which categories.

The set-up is so (simplified):

Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4

Categories
id
1 Category A
2 Category B
3 Category C
4 Category D

Suppliers_To_Categories
id supplierId categoryId
1 1 1
1 1 2
1 1 3
2 2 3
3 3 1
3 3 3


What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.

Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...

I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
SELECT Suppliers.*
FROM CatToSupplier INNER JOIN
Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
WHERE (CatToSupplier.CatId = @CatId)

This is probably really easy, but I've become unstuck!

View 13 Replies View Related

SQL 2012 :: How To Select Top 200 To 300 Rows From A Table

Mar 9, 2015

Select top 100 * from tab1 order by col1 ---gives me the top 100 order by col1

Select top 200 * from tab1 order by col1 ---gives me the top 200 order by col1

How do I select the top 100 to 200

View 3 Replies View Related

How To Create SELECT For Rows In Table

Oct 20, 2014

I have two db-tables:

"TAB01" with structure:

CREATE TABLE TAB01 (
ID int NOT NULL,
Name varchar(64) DEFAULT NULL,
PRIMARY KEY (ID)
)

It contains only name of some object.And "TAB02" with structure:

CREATE TABLE TAB02 (
ID int NOT NULL,
TAB01_ID int NOT NULL,
PositionA int,
PositionB int,
StringVal varchar(32) DEFAULT NULL,
PRIMARY KEY (ID)
)

which contains following data:

ID|TAB01_ID|PositionA|PositionB|StringVal|
1|1|1|1|A|
2|1|1|2|B|
3|1|1|3|C|

[code].....

and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:

TAB02.ID|TAB01.Name|TAB02.PositionA|TAB02.PositionB|
1|ABC|1|1|
10|DEF|2|3|
25|JKL|4|3|
30|MNO|5|1|
36|MNO|5|7|
41|PQR|6|4|
46|STU|7|2|

Data inserted in "TAB02" represents real-life structure:

ID|Name|P01|P02|P03|...|Pxy|
1|ABC|A|B|C|D|E|A|D|
2|DEF|J|K|A|B|F|F|B|
3|GHI|B|A|C|A|F|G|A|F|
4|JKL|F|E|A|B|B|E|E|
5|MNO|A|B|C|B|A|D|A|B|
6|PQR|D|E|F|A|B|A|F|
7|STU|A|A|B|B|E|B|E|

View 1 Replies View Related

Select Rows Once That Do Not Exist Twice In Same Table

Dec 6, 2007



Hi,

I have a table of row IDs, order numbers, and status. Status is a value of 1 or 2. Some rows only exist as a 1, some only as a 2, and some exist twice -- once as 1 and once as 2. I want to select orders that are 2 as well as rows that are 1 but do not also exist in the table as 2. Ultimately I will only see each order number once. Is there a better/faster way than doing multiple selects and comparing them to each other?

Example data:

RowID OrderNum Status
1 101 1
2 102 1
3 102 2
4 103 2

Desired results when selecting ID and ordernum from that table:
1 101
3 102
4 103

Thanks.

View 4 Replies View Related

Is There A Way To Alphabetically Select Rows From Table?

Oct 9, 2006

I have SQL mobile database. Due to the size and speed, I would like to select only rows that start with a certain set of alphabets. Is there a way to do this? I just don't want to query everything and use only a small number of rows since the recources on the device is limited.

Thanks.

View 8 Replies View Related

How To Select Specific 2 Rows Out Of A Huge Table

Jul 24, 2013

I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .

Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.

View 6 Replies View Related

Affecting SELECT Rows To A Table Variable

Aug 16, 2006

Hi,

I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!

ex :
DECLARE @MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)

SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?

View 3 Replies View Related

Get The Id Of Inserted Rows --&&> Insert Into Table1 Select * From Table 2 ?

Apr 17, 2008



hi

I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:

insert into table1 select * from table2

If I get the value of the @@identity, I always get the last inserted record.

Any idea how to get the ids of all inserted values?

Thx

Olivier

View 13 Replies View Related

Select Multiple Rows Of Data Not From Any Pre-existing Table

Apr 29, 2008

I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?

View 3 Replies View Related

SQL Server 2012 :: Select Rows With Sum Of Column From Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here are my table definitions and data:

/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,

[code].....

View 0 Replies View Related

Select Multiple Rows Based On Date Interval From Table

Apr 7, 2015

I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:

Original:

Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20

Resulting view:

A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01

I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.

View 1 Replies View Related

Randomly Order Selected Rows From Table - Select Procedure Vs Application

Jun 21, 2012

I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?

Which is faster? There will be about 100 rows returned with 10 columns.

View 5 Replies View Related

Transact SQL :: Select 1000 Rows At A Time From / Into A Large Temp Table?

May 12, 2015

I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.

In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.

For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.

Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?

Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).

View 6 Replies View Related

SQL Server 2012 :: Insert Multiple Rows In A Table With A Single Select Statement?

Feb 12, 2014

I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).

Each row will have the same item, but with a different task type.ie.

TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'

How can I do this with tSQL using a single select statement?

View 6 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 Replies View Related

SELECT * Not Returning Any Rows, But SELECT COL_NAME Does!

Jul 20, 2005

I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.

View 9 Replies View Related

Is There A Method To Convert Select * From Table To Select Field1,field2,...fieldn From Table ?

Nov 29, 2007

Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
 
Thanks

View 1 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.

Any sort of help would be highly appreciated.

Thanks,

View 2 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

Any sort of help would be highly appreciated.

Thanks,

View 1 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related

Select * From Table Is Processed Much Faster Than Select MyField From Table ¿?¿?

Oct 1, 2007



I have a query that has the following structure

Select *
From Table
Where Condition And ... (some 'Exists' conditions)

When I run the query using field names the query gets much slower, and I cannot understand Why!


Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)


I'm talking about three times slower using the Select MyField sintax.

Any ideas???

View 8 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 
 

View 3 Replies View Related

Select All Rows Except Some Rows

Feb 3, 2006

Hi all,

Is it possible to say something like

INSERT INTO Book
Book.* EXCEPT (a,c,d)

Something like WITHOUT
DESELECT
EXCLUDING
LESS

Thanks

View 4 Replies View Related

Delete Rows From One Table Using Rows From Another Table

Jul 23, 2005

Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"

View 14 Replies View Related

Fetch Odd Rows And Even Rows From A Table

Apr 4, 2008

Dear Friends,
Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way.
Thanks all!

kiruthika
http://www.ictned.eu

View 3 Replies View Related

I Want To Select All Rows Between 10 And 20 .......

Feb 15, 2007

I want to select all rows between 10 and 20 from aview
 how can I do this?

View 3 Replies View Related

Select Last Rows

Feb 13, 2001

How can I SELECT the last rows inserted in a table?

View 2 Replies View Related

Select Rows By One For Each Value

Oct 12, 2004

Tell me please how to select from a table only rows with different value of one of fields (n_pr field). But I need also to select some other fields where the values can be different for one n_pr field's value and select only first value of them. For getting list of n_pr field's values I can use derived table of query with DISTINCT operator but I don't know how to join with other fields of the table to get only one row for each value of joinning field n_pr. I am using SQL Server 2000 and this DBMS can use TOP operator to select only one row but all the ways I've tryed affect all the query and I get one row at all instead of one for each n_pr field's value. Also I need in this query to connect one more table. So for efficiency it better to do it in one query or in one stored procedure that return a recodset as it needs.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved