Query help - Select specific rows from a table?


!
can anyone help me to retrive some rows from the midle of the table, if each of the column are of type varchar
suppose i have a table containing 100 rows. then is there any way by which i can retrive, for example, rows 30 to 40
Thank u so much 4 ur consideration.
Shoeb
Posted On: Friday 26th of October 2012 12:00:20 AM Total Views:  384
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Referencing data from a query

I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure: Quote: CREATE PROCEDURE dbo.SP_Check_Login ( @arg_UserEmail VARCHAR(255), @arg_UserPassword VARCHAR(255)) AS BEGIN SELECT a.userArchived,a.userPasswordDate FROM app_users a WHERE a.userEmail = @arg_UserEmail AND a.userPassword = @arg_UserPassword END; GO What I want to do is some conditional statements on the query results. i.e: IF (userArchived = 1) RETURN "Archived" ELSE IF (userPasswordDate < dateadd(month,-3,getdate()) RETURN "UpdatePassword" ELSE RETURN "OK" So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code
VIEWS ON THIS POST

175

Posted on:

Friday 26th October 2012
View Replies!

search a column of comma delimited numbers (was "help with sql query")

I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query: SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%' Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help.
VIEWS ON THIS POST

186

Posted on:

Friday 26th October 2012
View Replies!

add checkbox column to access table (was "SQL query help.., please, any one??")

good people of this forum.. I am working on an eccess db, and using asp to update, change and so on... I want to add dynamicly a field to the db, using the ALTER TABLE command, but for some reson I can't find a way to make it work, the field type I am trying to add is boolean, and I want it to show up in the actual db as a checkbox, and not as a bit field (0/1), so I've been trying this and non of them work, and I couldn't find any sites with examples for this... any help would be very appricated..., this is what I've tryed so far and didn't work: "ALTER TABLE [Users] ADD [CHILD] boolean" "ALTER TABLE [Users] ADD [CHILD] boolean False" "ALTER TABLE [Users] ADD [CHILD] boolean(f)" "ALTER TABLE [Users] ADD [CHILD] boolean f" me out on this\t
VIEWS ON THIS POST

314

Posted on:

Friday 26th October 2012
View Replies!

Greater/Less than query from within subsets of results

I think I'm trying to do a simple query on maximum date. I've got 100 tools that have been used over the past three years. Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more. Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago. I have a list of tools and a list of times each tool was used. I think I'm going to have to do a search that for each tool what was the times it was used. That I can do. What I'm not sure of is how to then pull only the latest date for each tool. Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
VIEWS ON THIS POST

303

Posted on:

Friday 26th October 2012
View Replies!

Trying to pull data from 2 tables into a query

I'm trying to create a SQL query to pull together a job info table and a customer info table in an Access DB. We can't have a unique ID on the customer that we can store in the job table because the customer info is dumped from Exchange and the ID (row number) in the cust_List table will change each time we export from Exchange. What I need to do is go through jobs, get the CO_ID, the CONTACT_LN and the CONTACT_FN and then match it against Customer_List>Account, Lastname, Firstname. Any ideas
VIEWS ON THIS POST

325

Posted on:

Friday 26th October 2012
View Replies!

specialization query problem

Two queries need to be joined such that the TSL-named column values are all unique. That is, only unique TSL values are returned in the result set (with the highest degree of specialization represented by ISO3166 - Country/Region Codes.) Query 1 returns: TSL ISO3166-1 ISO3166-2 x -- US y -- US Query 2 returns: TSL ISO3166-1 ISO3166-2 x US-WA -- Deed join result: TSL ISO3166-1 ISO3166-2 y -- US x US-WA -- How do I create a query such that if a TSL value exists in both query 1 and 2 that is the same, x in this case, that the row from query 2 (US-WA is more specialized than US) is taken over query 1 results. The two queries are actually from the same table.
VIEWS ON THIS POST

176

Posted on:

Friday 26th October 2012
View Replies!

Problems with query statement

I have the following query: Code: SELECT COUNT(Hit.fileID) AS counter, Hit.fileID, FileDetail.* FROM Hit,FileDetail WHERE (FileDetail.fileID = Hit.fileID) GROUP BY Hit.fileID Tables look like this: Code: Hit: [ hitId - pri key fileId - for key date other stuff ] FileDetail: [ fileId - pri key file name file type ] I want to count the number of times that each file appears in the hits table. The output I require is : fileId, name, type, number of times in hit table.... I know this is probably pretty simple but I keep getting silly errors
VIEWS ON THIS POST

170

Posted on:

Friday 26th October 2012
View Replies!

View the SQL query out-put in horizontal format.

I have the following SQL query that I like to view the out put in horizontal format: Select ID, First_name, Last_name from ABC Instead of getting out-put like Id First_name Last_name 1 Jim Smith 2 Tom Jones I like to see the out-put like: Id 1 2 First_name Jim Smith Last_name Tom Jones Please advice.
VIEWS ON THIS POST

306

Posted on:

Friday 26th October 2012
View Replies!

query help

Im trying to do a query to extract all the records from one table that are not in another table. Essentially the unique records from two tables. Not sure of the query though. Any ideas.
VIEWS ON THIS POST

166

Posted on:

Friday 26th October 2012
View Replies!

Use calculated field in same query

Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work
VIEWS ON THIS POST

186

Posted on:

Friday 26th October 2012
View Replies!

MS query help

This is for a college project: I am trying to average all records of the same ID but also have it do the same for all of the IDs within the same query. Is their a way of doing this From Tables Sow/Gilt ID Litter NumberBornLive
VIEWS ON THIS POST

205

Posted on:

Friday 26th October 2012
View Replies!

Help with query. Joins? Unions?

I have a few tables that I want to query and I need the output back in a certain way. This is an example of my problem. Users Table: ID, UserName 1 , Fred 2 , Bill DVD Table: ID, DVDName 1 , XMEN 2 , Terminiator 2 3 , MontyPhthon Holy Grail VHS Table: ID, VHSName 1 , Terminator 2 , StarWars DVD Users table: ID, UserID, DVD_ID 1 , 1 , 1 2 , 1 , 3 3 , 2 , 1 4 , 2 , 2 VHS Users Table: ID, UserID, VHS_ID 1 , 2 , 1 3 , 1 , 2 So, each person might 0 or more DVDs and 0 or more VHS. What I need to output is a flat table something like this. User, DVD , VHS Fred, XMEN , Fred, MontyPhthon Holy Grail , Fred, , StarWars Bill , XMEN , Bill , Terminator 2 , Bill , , Terminator What I am getting is something like. User, DVD , VHS Fred, XMEN , StarWars Fred, MontyPhthon Holy Grail , StarWars Bill , XMEN , Terminator Bill , Terminator 2 , Terminator
VIEWS ON THIS POST

145

Posted on:

Friday 26th October 2012
View Replies!

SQL query brainfade

Hi . New guy here. Ok, here's a project that I have. I'm wondering if anyone can give me insight if it's possible. Ok, I have two different tables. One contains a size code for an item we sell such as (along with measurements specific to that code, that are not important at this point) For information purposes, the table name is "size_data" and the value below is "size_code", which is the primary key in the table. THS-101 THS-102 THS-103 .... ... . I have another table that contains that material data that they're using. This has a code as well of : 700 701 702 727 728 ... .. . That table is called "material_data" and the value is "material_code", which is the primary code in that table. Now, what I want to do is concatenate the values, but in every combination possible so that we can document every possible combination of the numbers. For an output example: THS-101-700 THS-101-701 .. .. . THS-102-700 THS-102-701 THS-102-702 .. .. . Is a query like that even possible with SQL I can't figure it out and I'm spinning my wheels. I just want to take the first entry of one table, concatenate it to all the combinations of the second table.........go the next entry of the first table and concatenate it to all the combinations of the second table, and so on. Anybody able to help me out on this one\t
VIEWS ON THIS POST

163

Posted on:

Friday 26th October 2012
View Replies!

sql query in an asp page

hi..i am trying to read in record from a databse located in the web server... i am trying to do this sql query in an asp page..so this is an asp code...i have written the code as this just asking that is there any thing wrong with this sql query....because my page isn;t showing...and i donno if it is the right way to connect different tables...please help
VIEWS ON THIS POST

166

Posted on:

Friday 26th October 2012
View Replies!

joining a view to match main data in search query

I am currently building a telephone directory, i'm at the stage where I have the search functions runing smoothly. Except the way I have the database set up is that the main table holds the business type as a numeral which was entered from another table with coresponding names. I have built views to tie these two tables together. Now once a person searchs the listings they currently recieve a page of results, it is here that the problem occurs. I need to to in my sql statment tie the main table of data to the view to pull the business type name out instead of the business type numeral which is contained within the main data table. Here is my current Sql Statement: SELECT * FROM dbo.MAIN, dbo.VIEW_BUSINESS_NAME WHERE ENTRY_TYPE LIKE 'varSearchType' AND LAST_NAME LIKE '%varName%' AND INITIAL LIKE '%varInitial%' AND STREET_NAME LIKE 'varStreet' AND STATE LIKE 'varState' AND REGION LIKE 'varRegion' AND BUSINESS_TYPE LIKE 'varBusinessType' AND dbo.VIEW_BUSINESS_NAME.Expr1 = dbo.MAIN.BUSINESS_TYPE ORDER BY LAST_NAME It is being put together in dreamweaver mx. This is returning a multiple list of the one business repeated for as many listings as there are for that business type! Hope this makes sense it's very early in the morning.
VIEWS ON THIS POST

228

Posted on:

Friday 26th October 2012
View Replies!

sql query question...

Here is a snippet of my query..... select top 500 MAX (distinct userip), count(*) as hits, username, sum( convert( bigint, bytecount))/1024 as bytecount_MB, 0 as blocks from webproxy.dbo.transactionsunday group by userip, username union select distinct userip, 0 as hits, username, 0 as bytecount_MB, count(*) as blocks from webproxy.dbo.transactionsunday where status = '403' group by userip, username How can I get the same 500 userips from the first part, queried in the second part
VIEWS ON THIS POST

189

Posted on:

Friday 26th October 2012
View Replies!

SQL Data from query to Excell file

Hi I have a simple database in SQL Server 2005... I made a simple query like this.... Select CitiesName,CitiesSize From Cities I save this query.......Now i want to run it from excell 2003 or 2007.... I mean the 2 columns that query brings(Cities_Name, Cities_Size) bring it in excell file.... I know that i can input data in Excell from a Table... But can i input data from a query\t\t\t\t\t\t Because my table have many fields and i want only these 2.... Is there any other way\t\t
VIEWS ON THIS POST

144

Posted on:

Wednesday 7th November 2012
View Replies!

My query works in Enterprise manager but not when run from ASP

Hi all, This is REALLY confusing me (bashing my head on my desk!). For some reason, the following code works just fine when I run it via Enterprise Manager (modify the SQL of a table view just to test it). To avoid SQL injections, I am using SQL parameters instead of concatenating the values into the string. In Enterprise Manager, it asks me for the two parameters which I enter and there are no problems, it brings back the result as expected. SELECT *, (SELECT COUNT(*) AS c_counted FROM appointments WHERE appointment_confirmed = 1 AND appointment_contact = contacts.contact_id) AS contact_appointments, (SELECT COUNT(*) AS a_counted FROM appointments WHERE appointment_confirmed = 1 AND appointment_address = addresses.address_id) AS address_appointments FROM logins LEFT OUTER JOIN addresses ON (logins.login_id = addresses.address_client AND addresses.address_primary = 1) LEFT OUTER JOIN contacts ON (logins.login_id = contacts.contact_client AND contacts.contact_primary = 1) WHERE login_session = AND login_session_ip = AND login_active = 1 When I try to run exactly the same query from ASP, it comes back with the following error: Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "contacts.contact_primary" could not be bound. The ASP (classic - my comfort zone and this project is a little complicated) code I'm using is as follows: dim sql_cm, sql_cn set sql_cn = server.createobject("adodb.connection") sql_cn.open system_db set sql_cm = server.createobject("adodb.command") set sql_cm.activeconnection = sql_cn sql_cm.commandtext = sqlstatement sql_cm.commandtype = 1 sql_cm.prepared = true sql_cm.parameters(0).value = "1234abcd" sql_cm.parameters(1).value = "1.2.3.4" set select_results = sql_cm.execute set sql_cm = nothing set sql_cn = nothing I was thinking that there may be a problem with my code (I know there are other ways of doing it but I'm less concerned about that now), however (and here's the bit that I find really confusing), if I remove the part near the beginning... , (SELECT COUNT(*) AS c_counted FROM appointments WHERE appointment_confirmed = 1 AND appointment_contact = contacts.contact_id) AS contact_appointments, (SELECT COUNT(*) AS a_counted FROM appointments WHERE appointment_confirmed = 1 AND appointment_address = addresses.address_id) AS address_appointments ...it WORKS! So what is causing it to work fine via EM but not when called from ASP!!!!! Every little scrap of an idea is greatly appreciated because my forehead is really beginning to hurt!
VIEWS ON THIS POST

194

Posted on:

Wednesday 7th November 2012
View Replies!

Openquery question

Hi! Could somebody tell me what should I write at the linked_server variable , knowing that I only know the name of the server(tmav035a)
VIEWS ON THIS POST

150

Posted on:

Wednesday 7th November 2012
View Replies!

MS SQL query to get rows between m and n

I wanted the query which will return the rows between m and n. I cant do this using any column name as i wont be knowing the column names. I cant even use concept of temp table either. I tried SELECT * FROM [UtilityInput$] LIMIT 1 OFFSET 3 but dint work. I tried using rowcount and row id but i heard that doent work in MS SQL. Can some one help me with the query
VIEWS ON THIS POST

194

Posted on:

Wednesday 7th November 2012
View Replies!