Selecting A Certain Number Of Records?

Apr 16, 2008

Hi,

Here is a basic example of the issue I am having:

Table 1 columns - name, address, zipcode, favorite food

For table 2 I want to find how many zipcodes exists and also take 20% of the count

Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct

For example:

zipcode ct perc_ct
83746 10 2
93847 20 4

I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1.

Is this possible?

View 1 Replies


ADVERTISEMENT

Selecting Top 'x' Number Of Rows Through A Sp

Jun 24, 2008

Hello all.

Im trying to write a stored procedure which i can stipulate the top number of rows it returns. I know i use the following syntax:

SELECT TOP 5

And i know i need to pass the number of rows i want as a variable to the stored proceudre like:


@Top as Integer

But when i try and do the following. SQL Server complains about the syntax:

SELECT TOP @Top

Can anyone advise me how i should be doing this?

View 3 Replies View Related

Selecting A Specified Number Of Rows

Jul 30, 2007

I know I can use "First" to specify a number of rows to return from a query but is it possible for the number of rows returned to be based on a parameter, something like this:

SELECT FIRST @someNumber name, age
FROM friends
WHERE age > @ageInput

View 3 Replies View Related

Selecting One Greatest Number From Two Columns?

Jul 20, 2005

I have table1 and table2.In table1 I have a column of numbers, numbers1.In table2 I have a column of numbers, numbers2.I'd like to select the highest number represented in either column.Example:table1:column1--------------345565643656555676table2:column2--------------3456564556456456456456The number I would want would be 56456 since it's the largest numberout of all combined.How can I get that number with one select statement?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]

View 1 Replies View Related

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Selecting Number Of Products That Comes Under A Parent Category

Nov 27, 2007

  selecting number of products that comes under a parent  categorysqlserver2005CREATE TABLE [ProductCategoryAssociation]([CategoryID] [bigint] , --this is primary key of table Category[ProductID] [bigint] ,--this is primary key of table product) the above table binds a product to its category CEATE TABLE [Category]([CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category[Name] [nvarchar](255)[ParentCategory] [bigint] NULL,) I have a  Category"A" as a supper parentcategoryI have 1product in   Category"A"Category"A" have 2 child category A11,A12.  A11,A12 both have 2 products in the category (totally 4)Now I  can get the count of all product belong  Category"A"  (ie is 5= 1+2*2) fivelet 1 be the id of category "A"  Now the following querry give me the expected resultselect count(ProductId) from ProductCategoryAssociation where  categoryid=1 or   categoryid in (select categoryid  from Category where Parentcategory=1)  BUT if i have child for  A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosiderationso the following querry will do itselect count(ProductId) from ProductCategoryAssociation where  categoryid=1 or   categoryid in (select categoryid  from Category where Parentcategory=1 or Parentcategory in (select categoryid  from Category where Parentcategory=1))ie i nested one more selectbuthis has limitationsSo kindly give me a general solution to  check up to the las level of category

View 8 Replies View Related

Selecting Rows With Sums Equal To A Given Number

Nov 2, 2003

You are given say a pricelist of books. And you have to find out
all possible sets of books, each of them having total sum of book
prices equal to a given number.

set nocount on
if object_id('tempdb..#t')>0 drop table #t
if object_id('tempdb..#tt')>0 drop table #tt
create table #t (n int, price int)
insert into #t -- note asc order of book prices
select 1, 1 union all
select 2, 3 union all
select 3, 4 union all
select 4, 5 union all
select 5, 7 union all
select 6, 7 union all
select 7, 11 union all
select 8, 15 union all
select 9, 20 union all
select 10, 20 union all
select 11, 22 union all
select 12, 28 union all
select 13, 33 union all
select 14, 40 union all
select 15, 43 union all
select 16, 47 union all
select 17, 50 union all
select 18, 55 union all
select 19, 56 union all
select 20, 63
go
create table #tt (n int, price int)
go
declare @rows int, @p int, @sum int set @sum=16
delete from #t where price>@sum
set @p=(select sum(price) from #t)

if @p>=@sum
begin
set @rows=(select max(n) from #t)
declare @n int, @s int
set @n=@rows+1 set @s=0

while 0=0
begin
while @n>1
begin
set @n=@n-1
if @s+(select price from #t where n=@n)<=@sum
and @s+(select sum(price) from #t where n<=@n)>=@sum
begin
set @s=@s+(select price from #t where n=@n)
insert into #tt select n, price from #t where n=@n
if @s=@sum select * from #tt --- outputting
end
end
set @n=(select min(n) from #tt)
set @s=@s-(select price from #tt where n=@n)
delete from #tt where n=@n
if @s=0 and (select sum(price) from #t where n<@n)<@sum break
end

end
drop table #tt
drop table #t

Result for @sum=16 (for e.g. @sum=76 number of different sets = 196):
n price
----------- -----------
8 15
1 1

n price
----------- -----------
7 11
4 5

n price
----------- -----------
7 11
3 4
1 1

n price
----------- -----------
6 7
4 5
3 4

n price
----------- -----------
6 7
4 5
2 3
1 1

n price
----------- -----------
5 7
4 5
3 4

n price
----------- -----------
5 7
4 5
2 3
1 1
EDIT: added one more condition (in blue) into an IF statement.
Now it works incredibly fast.

View 4 Replies View Related

Selecting 5 Records

Jan 25, 2002

I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks

View 2 Replies View Related

Selecting Next 50 Records

Sep 11, 2007

Is it possible to use a SELECT to retrieve a Next 50 set of records?

Initial Query
Select top 50 email from table

What would the Query look like to retrieve the Next 50 records?

Thanks in advance.

View 9 Replies View Related

Selecting Like Records

Aug 31, 2006

This might be a simple question. I have a LIKE statement that is working fine, however I am not sure if something else is possible.

I can pull all records on a query for a person's name with a parameter value of "MARTIN". It will also pull records for "LYNN MARTIN". However, what if I would like to have that search also pull "LYNN M MARTIN"? Currently "LYNN MARTIN" is not finding "LYNN M MARTIN".

When the end user wants to search on LYNN MARTIN and that is what they input, I want SQL to find all records that match LYNN MARTIN and also find records that HAVE LYNN % MARTIN.

I hope this make sense? I guess I need to build my select statement using a WHERE LIKE statement, but I am not sure of the syntax.

View 3 Replies View Related

Selecting Distinct Records

Sep 1, 2007

Hi,
 I'm just wondering if someone can help me with some SQL syntax stuff.I want to take this sql statement: 
"SELECT TOP 50 tblProfile.chName, tblProfile.intCount FROM tblProfile, tblLinks WHERE (tblLinks.MemberID = tblProfile.MemberID) ORDER BY tblLinks.dtDateAdded DESC;"
 and select only unique "chName's" records 

View 9 Replies View Related

Selecting Unique Records

Sep 17, 2007

Hello Everyone and thanks for your help in advance.  I have a SQL Server Table wtih approximately 100,000 records.  I need to determine if there are duplicate records in this table.  My probelm is that there is a unique ID column that was added for each row, so I'm not exactly sure how to filter the rows.  Any help on this would be greatly appreciated.  Thanks.

View 4 Replies View Related

Selecting Records Randomly With SQL

Jan 29, 2008

I'm looking for a bit of SQL code that will select some entries randomly from an SQL table.

For instance I'd like to feed a parameter in that contains the number
20, and the returned record contains 20 randomly and distinct selected
records.

Anyone know how this can be done? (never came across randomly select records) Appreciate any help  

View 6 Replies View Related

Selecting Random Records

May 11, 2008

hi again,
i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure??
thanks

View 3 Replies View Related

Selecting Most Frequent Records

Jul 26, 2004

I have a reviews table where all reviews are submitted. On the main page I want to display the 10 most reviewed products. I have a Product_ID column in this table which identifys the product. How can i write a query which will select the product_ID of records which have the most frequent product_ID's?

I came up with something like this:
"Select Top 10 Product_ID, COUNT(*) AS Occurances FROM reviews GROUP BY Product_ID ORDER BY occurances DESC"

But it does not work.?? It says "Declaration expected" as error

View 5 Replies View Related

Selecting Records In One Table But Not In Another 2

Dec 13, 2004

Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.


SELECT DateOfStats
FROM table1
WHERE (DateOfStats NOT IN
(SELECT dateofstats
FROM table2)) and (DateOfStats NOT IN
(SELECT dateofstats
FROM table3))


Thanks for the help in advance.

View 1 Replies View Related

Question About Selecting Records

Jan 12, 2005

I have a mssql database like this:

Table1:
ID GuestName Agent CheckInDate OtherColumn
1 Guest 1 Peter 1/11/2005 whatever
2 Guest 2 Peter 1/11/2005 whatever
3 Guest 3 John 1/11/2005 whatever
4 Guest 4 John 1/13/2005 whatever
5 Guest 5 Peter 1/11/2005 whatever

I want to display it group by same Agent and CheckInDate. Like this:

Agent CheckInDate Records
Peter 1/11/2005 3
John 1/11/2005 1
John 1/13/2005 1

How can I do this? Please help, thank you.

View 4 Replies View Related

Selecting Only Those Records Which Are In Another Table

May 25, 2005

i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.

View 2 Replies View Related

Selecting Last 'n' Records From Table.

Nov 17, 2005

I was given this query but could not build it successfully.
can anybody help me out.

Consider the following scenario:

we have a table with single field of type Varchar(100).
we have few hundred records in it.
we want to retrieve last 'n' records from table.

i know this is bad idea but we need to have some solution to work it out.
i'm breaking my head but to no use.

Please help

View 3 Replies View Related

Selecting 10 Random Records

Mar 1, 2004

How would I create a statement that would select 10 random records from the SQL DB?

View 5 Replies View Related

Selecting Min And Max Of Multiple Records?

Sep 27, 2013

What I need is the start and end time of each task, but the issue is there is no unique task number to bind them together.. So for instance the task starts with 'Open-Submitted' and ends with 'Task Approved'. The issue is there can be multiple occurrences in the same file number. I need to be able to split these into multiple tasks with the associated start and stop times.

File IDDatetimesTask Event StatusTask Event NameTask IDEvent ID
File 16/3/13 16:33Open-SubmittedTask is retrievedTSK-12345612345
File 16/3/13 16:44Open-ApprovedTask ApprovedTSK-12345623456
File 16/20/13 18:11Open-SubmittedTask is retrievedTSK-12345634567
File 16/21/13 14:42Open-ApprovedTask ApprovedTSK-12345645678

View 3 Replies View Related

Selecting Unique Records

Mar 21, 2014

I am trying to create a select query similar to the following but the problem I am having is that I want to only select one record where there may be several with the same dw_order_no. I have tried various ways using SQL developer but without success

SELECT VE_EZP_ORDER_TRANS.EZP_BILL_STATUS AS EZP_BILL_STATUS1,
VE_EZP_AGED_CUSTOMER_DEBT.SURNAME,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE_CATEGORY,
VE_EZP_AGED_CUSTOMER_DEBT.DEBT_AGE,
VE_ORDERLINE.DW_ORDER_NO,

[code]...

View 3 Replies View Related

Selecting Max Records With A Sequence

Jan 30, 2006

I am having trouble selecting only one record in a list with a sequence, example:

name addseq address
================================
bob, doe 1 123 str.
bob, doe 2 456 ave.
jane, doe 1 789 blvd.

What I want to do is select the records that are current which would be:

name addseq address
================================
bob, doe 2 456 ave.
jane, doe 1 789 blvd.

Please help with some ideas.

Thanks

View 1 Replies View Related

Selecting Duplicate Records

Jun 28, 2007

Is there a way to see a list of duplicate records??
EG There is a field named "Invoice" in a table named "Orders" and I want to see only records where the same invoice shows more than once.

Sample output:

Invoice--Partno
123------a66
123------9pp
123------k33
5988-----j22
5988-----bx1
66-------pq1
66-------333

etc......

Thanks
Mike

View 1 Replies View Related

Selecting Records By Date - Need A Little Help

Mar 15, 2006

Good Morning,I have a view that contains rate information, contractIDs, and effectivedates.I need to select the rate info based on contractID and date.I can provide a date and contractID, and I need to select the rate info forthat contract where the effective date is <= dateprovided.I need the 1 record that is closest to that date.I am thinking something with max() perhaps. Any ideas? The <= effectivedate will return several rows, I just need the one closest to the date Iprovide.Thanks for any advice,CK

View 2 Replies View Related

Selecting Distinct Records Through CTE

Jan 11, 2013

I have a table contains information related to sales:

SO number Order Date     Customer SellingPerson
1001          2012/07/02     ABC          Andy
1002          2012/07/02     XYZ           Alan
1003          2012/07/02     EFG          Almelia
1004          2012/07/02     ABC         John
1005          2012/07/02     XYZ          Oliver
1006          2012/07/02     HIJ           Dorthy
1007          2012/07/02     KLM          Andy
1008          2012/07/02     NOP         Rowan
1009          2012/07/02     QRS          David
1010          2012/07/02     ABC          Joey

Now, i want to write a query using CTE that gives me first five distinct customer in result set:

SO number Order Date     Customer SellingPerson
1001          2012/07/02     ABC          Andy
1002          2012/07/02     XYZ           Alan
1003          2012/07/02     EFG          Almelia
1006          2012/07/02     HIJ           Dorthy
1007          2012/07/02     KLM          Andy

I wrote this query :

With t(so_number,order date,customer, SellingPerson)
as
  (select top 5 so_number,order date,customer, SellingPerson from t)
 select distinct billingcontactperson from t order by so_id

And getting this error:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 't' does not contain a top-level UNION ALL operator.

View 9 Replies View Related

Selecting Records Using Cursor ???

Apr 2, 2008

hi ,
how can i have a stored procedure that selects records using cursor
and returns the cursor ?


thanx in advance

View 6 Replies View Related

Selecting Records Based On Two Conditions

Oct 25, 2007

Hi! I have a table Tbl1 has to columns:
A          B
_________
Ibm       Me
Sony     Me
Me        Bob
Me        Frank
I'd like to select all rows where B=ME and A=Me Thanks for the help

View 2 Replies View Related

Selecting Records By Random Order

Sep 22, 2000

Hello,

I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.

SELECT * FROM QuestionMaster.

I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.

SELECT Convert(int,RAND()*97)

Thanks in a million.

Sarika

View 1 Replies View Related

Selecting A Range Of Records In A SQL Statement

Feb 12, 2005

I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).

I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?

I can use TOP 10 to get the first set of records for the first page eg.

SELECT TOP 10* FROM Suppliers

...but how do I get between 11 and 20, 21 and 30 and so on?

I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.

Someone gave me the syntax..

SELECT *
FROM employees
LIMIT 10,10;


..but it didn't seem to work.

Thanks for any help!!

View 14 Replies View Related

Selecting Distinct Records Quickly

Mar 26, 2007

Good day,

I have a table of approximately 10 million rows. The table has 3 field making up the key, namely:
ID, Date, Program

I need to extract all the distinct Program's from the table.
I have don so with:
Select distinct Program from table
This unfortunately takes roughly 2 minutes which is far to long. Is there something I can do to help speed this process up?

Thanks in advance.

View 14 Replies View Related

Selecting The Records In The Present Week

Dec 3, 2007

Dear All,

I want to retrive the records if are created in a particular week.
if i pass 03/12/2007 , i want get records which are created in that week

View 11 Replies View Related







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