Selecting A Desired Sequence Of Records Without Where Condition

Apr 12, 2001

Dear Friend,

How to select a desired sequence of records without where condition?

For example I want to select the first 100 records I am using

SELECT TOP 100 * FROM EMPLYEETB;

Like this I want to select the records starts from 101 to 200.

Is it possible? If yes how?

Thanks in advance.

Lovingly,
Meyyarasu

View 1 Replies


ADVERTISEMENT

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

Separating Desired From Undesired Duplicate Records

Nov 20, 2007

I know of several methods to remove duplicate records but I recently encountered a unique situation where some duplicate records were actually acceptable.

Here is my situation:

I have a table that contains records of individuals who have children so 1 person can have 3 children with different birthdates; but there is also a field that has a specified language. The challenge arises when an individual may have only 1 child but has entered a single record twice. Once with a specifed language and again without which produces a default value of UNKNOWN.

I need to be able to remove this record without affecting records that may have a record entered twice as well; having 2 children for instance but they may also have a specified language in one record but a default value of UNKNOWN for their second record.

So I can't eliminate the unwanted duplicates by filtering out records that have UNKNOWN because I would also remove individuals that I need.

EX:
firstN | lastN | address | lang | childs birthdate
John Doe 210 Somewhere Ave ENG 1993-10-09
John Doe 210 Somewhere Ave UNK 1993-10-09
Jane Doe 210 Anywhere Ave ENG 1969-12-23
Jane Doe 210 Anywhere Ave UNK 1958-04-15

How could you remove the duplicate for John in this example without affecting Janes duplicate record which is actually ok because she apparently has 2 children with different birthdates whereas John's duplicate record is obviously created because it was entered twice; once without entering
a language and the second time specifying the language?

I have tried a number of things short of creating a cursor which isn't really the best way to resolve this issue since there are millions of rows.

Anyone out there have any input that be helpful? Or has anyone ever had this similar issue? I would be interested in knowing how you addressed the problem.

Any help is appreciated. :)

View 2 Replies View Related

Selecting Tables That Meet Certain Condition

Aug 5, 2004

Hi there,

I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.

So I just want to use a cursor on a table list that meet the criteria:

1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '


Example

declare @strqry varchar(1000)

declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable

while @@FETCH_STATUS = 0

begin

Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '

exec(@strqry)

fetch next from dailyYear into @DelTable

end

close dailyYear
deallocate dailyYear

Any help would be greatly appreciated!!

Thanks

S

View 3 Replies View Related

Transact SQL :: Selecting First Row After Date Matching Condition?

Sep 22, 2015

I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.

The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.

The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.

I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.

I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:

SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...

I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.

View 9 Replies View Related

Sequence Number For Records

Nov 15, 2007

Hi...

I have Sql statement more like this

SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a


rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH



How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :

rec_num employeeID employeeName employeeDepartment


1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH

View 4 Replies View Related

Multiple Records With Condition

Aug 29, 2006

I know how to query up duplicate records, here is what I am using to do so:

Select PhoneNum, Count(PhoneNum) as NumOccurances
From Tester
Group By PhoneNum
Having (Count(PhoneNum) > 1)


when the duplicates arise, I expect them to have unique CallResultCode values, and I would like to make a priority of which value stays and which one gets dumped, keep in mind that I am a SQL noob.


Registered Linux User #365191

View 1 Replies View Related

Condition Before Insert Records ?

Feb 7, 2008



Hi guys,

Help me to write my query,

I want to write a insert statement but before insert statement run i need to check a condition like if the same record is already existing or not ? how do i do this using transact -SQL

I mean i have a table call Employee like this definition

ColumnName Datatype
EmployeeID INT
Name Varchar(255)


Records like this

EmployeeID Name
1 John E Mathew
2 Ethel Elizabeth


Ok Before i insert a new record , i need to check if a emplyee name call ="John E Mathew" already in the table or not if Employee Name call ='John E Mathew' not exisits only it should execute my insert statement .

how do i write my SQL ?

regards
suis





View 9 Replies View Related

Update All The Records Of A Table On A Condition

Sep 20, 2007

Hi
I have a two tables as follows

Table Category
{

ID PK,
LastUpdate DateTime
}

Table Master
{

ID PK
Catrgory DateTime
}

I wanted to update Catrgory coulmn of all records in the Master table with the Value of LastUpdate of the CategoryTable the where the ID of the both the table are same

Can any one please let me know the query

~Mohan

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

Transact SQL :: Get Records Based On Condition In Server

Nov 22, 2015

I have a question about SQL Server.

Table patient:

create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),

[Code] ...

Table Patientref:

create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),

[Code] ...

Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)

If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.

Examples: patient

pn |  code  |  date      |   doctorcode
2  |  10    |2015-02-12  |   101
2  |  10    |2015-02-13  |   102
2  |  10    |2015-02-14  |   103

Table : Patientref:

pn |  code  |  sdate      |   edate      | Status
2  |  10    |2015-02-08   |   2015-02-19 |  4
2  |  10    |2015-02-09   |   2015-02-19 |  2
2  |  10    |2015-02-10   |   2015-02-19 |  2
2  |  10    |2015-02-11   |   2015-02-18 |  1

Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.

pn = 4donot have sdate and edate and status values dut not fall conditon 

Based on this, the desired output is below:

pn |  code  |  date      |   doctorcode | sdate     |edate      |status
1  |  10    |2015-02-19  |   100        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   101        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   102        |2015-02-19 |2015-03-24 | 2
2  |  10    |2015-02-12  |   101        |2015-02-10 |2015-02-19 | 2

[Code] ...

I tried it like this:

select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
 outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr 
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
 order by case when pr.status=2 
then 1 when pr.status=4 then 2
 when pr.status=3 then 3 
when pr.status=1 then 4 end ,pr.sdate 
 )pr

but this query not given expected result.here when dos not fall between sdate and edate  that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.

View 7 Replies View Related

How To Filter Out Records With A Condition In The Data Flow?

Apr 3, 2008



Hi, all experts here,

I am desperate to need you help.

Could you please give me any advices on how to filter out the records through out the data flow by any particular condition? E.g. In my case, I want to filter out rows with null id (will get rid of those rows with null id which are not matched in the look up component)? Hope it is clear for your help and I am looking forward to hearing from you for your help and thank you very much.

With kindest regards,
Yours sincerely,

View 5 Replies View Related

Transact SQL :: Get Only Those Distinct Records Which Satisfied Condition

Jun 3, 2015

I need to write a query on a table..

table defination .. table Client_Master
file_id int identity(1,1) primary key 
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit

has Data ..

FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1

Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03  out of which 3 are rejected ..

Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE  from Client_Master where all files has been rejected ..

means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..

I have wrote as below .. got the result but not satisfy with the query

SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM  dbo.FILE_MASTER 
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )

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 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 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 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







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