SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTime
FROM NameTa
ORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
Hi..
As everyone knows that there's a keyword in MySQL that is used to limit the records in SELECT statement as
SELECT * FROM Table LIMIT 40,10
means that select from record 40 to onward 10 records.
In SQL Server it is a long way with nested select ... can someone provide an alternate or we have to use the Nested SELECT?
Thanks
I have a requirement to mark duplicate records when I pull them from the database.
However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.
The code I have below creates a column called Dupes but marks all the duplicates - including the first one.
Is there a way to only mark the 2nd, 3rd, 4th etc record ?
SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,
Dupes = (select count(id) from CallLogswhere (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)AND DealerId= 'hdsh' AND CSRStatus IS NULLand datediff(d, logdate, getdate()) <= 21),
FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatusleft Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)And DealerId='hdsh'And Logdate Between '08/01/2007' And '08/31/2007'
I've searched high and low for info on how to do this... If anyone has an idea, I'd really appreciate it.
I have three tables: PEOPLE, PROJECTS, COMMENTS. I want users from the PEOPLE table to retrieve a list of PROJECTS and be able to add/edit COMMENTS on those projects.
The tables look like:
PEOPLE
people_id (primary key)
first_name
last_name
PROJECTS
projects_id (primary key)
project_title
project_summary
COMMENTS
comments_id (primary key)
projects_id (foreign key)
people_id (foreign key)
comment_detail
I'd like to be able to output something like what I have below, but I don't know how to loop over the comments/people within the select statement for the projects.
DESIRED OUTPUT
Project #1
Comment #1 by Person #1
Comment #2 by Person #3
Comment #3 by Person #8
Project #2
Comment #1 by Person #2
Comment #2 by Person #3
Comment #3 by Person #6
Etc...
I've done it before by just listing the projects and then providing a detail page with all the comments, but it's much less confusing to access all the comments from the same page, grouped by project.
Thanks in advance.
I have 2 tables in a 1: n relation. How can i get a select statement that the field in the n-relation with outputs, separated by a semicolon; Example: One person have many Job Titles
Table1 (tblPerson)
Table2 (tblTitles)
1, "John", "Miller", "Employee; Admin; Consultant"
2, "Joan", "Stevens", "Employee, Software Engineer, Consultant"
and so on .... 1 in select statement:
My example data as this:
ID code1 code2
1 a m
1 b n
1 c T
2 d m
2 e n
2 f n
3 g m
3 h n
3 i T
I would like to have a select stmt that check for all same ID and code2 does not contain T, then return the records
For example data: the query should return
2 d m
2 e n
2 f n
Is it possible to do this?
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
I am trying to build a select statement that will
1). find records where a duplicate field exists.
2.) show the ItemNo of records where the Historical data is ' '.
I have the query which shows me the duplicates but I'm stuck when trying to only show records where the Historical field = ' '.
Originally I thought I could accomplish this goal with a sub query based off of the original duplicate result set but SQL server returns an error.
Below you will find a temp table to create and try in your environment.
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int
[Code] ....
Ultimately I need a result set that returns 'ATMR10 MFZ N', and 'QBGFEP2050 CH DIS'.
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies View RelatedI have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View RelatedHello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedOk I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedHi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hello,I am selecting some articles and some comments related with it:SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmailFROM Articles aINNER JOIN Users u ON a.AuthorID = u.UserIDINNER JOIN Comments c ON a.ArticleID = c.ArticleIDI have 2 problems which I am trying to solve:1. Comments table also have an AuthorId So for each comment I also want to join to Users table and get the author name and email. How can I do this?2. I want to select all Articles even if it has comments or not. Can I use Inner Join or should I use Left Join? Is Outer Join still available in SQL 2005?Thank You,Miguel
View 2 Replies View Related Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote:
I have a simple table something like this
ID First Last
1 Bob Hope2 Charles Draw3 Mark Andrews
Etc, etc
I need the SQL statement select the last 10 records in the table, so if the highest ID id 82 I need all fields of records with ID 72-82
Help appreciated, i'm sure it must be simple enough buy mind is blank.
thanks
Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote: SELECT TOP 10 * FROM table WHERE id > 10
because even though my ID is auto numbered, but I have deleted some entries.
Does anyone have good solutions?
p.s I tried this and doesn't work either
Quote: SELECT TOP 10 * FROM table WHERE id IN(SELECT TOP 20 * FROM table ORDER by date DESC) ORDER BY date ASCENDING
TIA
-noPcz
Stijn writes "Hello,
I have SQL 7 with WINDOWS 2000 SERVER
My problem is that i am looking for a script
my db is for equipement inventory
how can i select all the records from a room,
if this room (room1)has less of 100 records he needs te go to the follow room(room2), en the next room until he's got 100 records,
sorry for my english"
Mack writes "I want to select last 2 records from a table AA which has following 5 records.
111
222
333
444
555
There is no primary key.
Any tip will be really appretiated.
Thanks for taking time to read it."
Hi,
How to write a query to get top 50 distinct records from a table.
I mean I have a table 'C1_Subscribers' with a field name 'Nickname'. Here I need to select top 50 non-repeated nickname only.
Can anybody can help me!!
Hello,Firstly, sorry for my english.I have problem with creating SQL statement. I am beginner and I think thatit is very easy to do. Look -I have to get only the last 20 records from table ABC (f.eg.) and accordingto that how to create the SQL statement? F.eg. SELECT name, forename, dateFROM abc WHERE....You know - how to finish the statement to get only 20 records lastlyinserted into the table?Thanks and I am waiting for helpfull answers.
View 3 Replies View Related I have a table, productTable, that contains a ProductID column, a bunch of attribute columns like manufacturer etc, and a GroupID. Many different productIDs can share the same GroupID which means that they are basically the same product with one different attribute such as color. What I'm trying to do is select all the products in the TOP N GroupIDs. I'm using this to paginate my query results. I tried this which does not work:
SELECT DISTINCT TOP 10 *
FROM productTable
WHERE GroupID NOT IN
(SELECT DISTINCT TOP 10 GroupID
FROM productTable
ORDER BY GroupID)
ORDER BY GroupID
This returns the TOP 10 rows, but I would like all the ProductIDs for the TOP 10 GroupIDs