Delete Records Not Matching Top25 In GroupBy

Jul 23, 2005

I have a table (tblA) that records the RecordID, UserID and
LastViewedDate (DateTime) of each record opened in tblB where RecordID
is the PK in tblB. I want to construct a query that groups all records
in tblA by RecordID, filters by UserID and keeps only the most recent
25 RecordIDs and deletes the rest.

This gets me a recordset of all RecordIDs filtered by UserID in tblA
but I can't figure out how to sort it by LastViewedDate DESC and to
eliminate those not in the Top25:

SELECT RecordID
FROM dbo.tblA
WHERE (UserID = 1234)
GROUP BY RecordID


Any help is appreciated!
lq

View 4 Replies


ADVERTISEMENT

Delete All Records In TblA Not Matching In TblB

Jul 23, 2005

I'm hoping someone can tell me how to construct a stored procedure thatdeletes all records in tblA not matching the PK in tblBThis gives me the recordset of all records in tblA with no matchingrecords in tblB (ID is the PK in tblB)SELECT a.IDFROM dbo.tblB bRIGHT OUTER JOIN dbo.tblA a ON b.ID = a.IDWHEREb.ID IS NULLthanks,lq

View 2 Replies View Related

How To Display Records Only Once Using Groupby In

Mar 31, 2008

in my sql query,when it run then it displays records as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

i want to display records only once like as follows

dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc

----------
49 Mechanical 03/14/2008 35 xyz

-------------

if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is

not contained in either an aggregate function or the GROUP BY clause.

so how can i design query so records display only once not repeated,or is any
alternative,to solve this .


my sql query as follows

SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as

dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,

dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,


op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type

,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,

m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')


uday

View 3 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

Returning Matching/Non Matching Records

Feb 4, 2007

Hi All

I have a strange request that might not be possible based on the laws of relational databases but I thought I'd give it a try.

I have three tables which for simplicity I will call A, B and C. Table A contains my master records, Table B contains user details and the final table contains some extra data

In my initial search when joining A and B, I return 100 records. I then need to search in table C for these 100 records based on a criteria. the expected result should return all 100 rows for the ones that match and also the ones that do not match. The problem is that in Table C, not all the 100 IDs exist, so there will not be a corresponding record. Unfortunately, our users still want to see all 100 records in the output. Is this possible

As always any help or direction would be appreciated.

View 5 Replies View Related

Delete Matching Rows From Target

Apr 27, 2008

Hi

I am very new to SSIS and currently was reading through various manuals but couldnt find a way to do a simple delete.

STAGE_ORDERS looks something like

order_id order_value
100 50000
300 70000
.....

Orders table looks like
order_id order_value
100 9000
200 1000
300 8000

now i want to use SSIS to delete the orders 100,300 which are present in staging table from the orders table .

How do i accomplish this task since i dont see a delete transformation.

How about updates if i want to update the order values from the staging table to the orders table what transformation can accomplish this

regards
Hrishy

View 3 Replies View Related

Most Matching Records

Oct 5, 2007

Hi,
This is a where clause I am using in a search.
 WHERE (ADDRESS_STREET LIKE '%' + @Search + '%' )
I am trying to do a search which returns the most matching record. For example if I have a record with Denver  as text . If I search for Denvr the spell error is intended , I will not get the result. How can I create a stored procedure to counter probable spelling errors and return  matching results in a ranked order.
Thanks

View 2 Replies View Related

Getting Matching Records

Jun 14, 2006

Hi,

I have the following table:

***********************************************************************************************
Sheet --- Cycle

Init SC --- 89
Post NCOA --- 89
Post Supp --- 89
Revised Final State Counts --- 89
Revised Final State Counts --- 94
***********************************************************************************************


Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?

Thanks

View 1 Replies View Related

Delete Rows From A Table Matching A Calculated Date

Apr 15, 2008





Hello I am new to SSIS and learning as I go. Any guidance to my questions would be appreciated.

I wrote a script that takes the current date and subtracts a number of days/months from this date. I then attempted to use an SQL Task as a select with a parameter using the calculated date from the script. I was not successful in doing this. While performing searches on the WEB with the hopes of finding a solution I came upon the following text in the Microsoft forum under EXECUTE SQL TASK.

When you use an OLE DB Connection Manager, you cannot use parameterized subqueries because the Execute SQL task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.


Having come upon this statement I moved on to putting together an OLE DB SOURCE with a Flat File Destination. The SQL that I wrote is:

SELECT BP_ID, INVC_NBR, INVC_DT, BUS_ADD_DT
FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

The flat file destination was mainly used to confirm the select.

Having confirmed my select, I changed the select in the OLE DB SOURCE as follows:

DELETE FROM DW.CUST_SALE_ADDR
WHERE (BUS_ADD_DT = ?)

I also removed the Flat File Destination. Needless to say when I tried to run the package I did not get very far as a package validation error was encountered since there were no output columns.

Can you share how I should go about peforming the delete as described from the table based on a calculated date? And am I not understanding the comment regarding the SQL Statement and the use of parameters?

Thank you and God Bless.








View 9 Replies View Related

Number Of Records Shown Are Not Matching

Sep 12, 2013

There is something I don't understand. When I use join

SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)

I am getting 76 orig_file records

But when I do

SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i right outer join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)

I am showing only 8 records under i.orig_file column and I am not sure why. What I need is to get all the AP_INVOICEDOCS in the matching orig_file records.

View 3 Replies View Related

Return Non-matching Records On Two Fields

Jan 18, 2008



Here is a very basic question that I have.

I have two tables, A and B. Both have a customernumber and a batchid. This combination is unique in both tables.

How can I pull back the records from table A that do not have a corresponding combination in B?

I know I could find the ones that do match and then exclude them using an inner join and subquery, but is there a simpler way?


THANKS!

View 1 Replies View Related

Returning Only Non Matching Left Join Records

Mar 3, 2015

How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.

I am checking for all parishes with no associated drivers. Currently I only have 2 of such.

The regular left join

select parish.name, driver.fname from parish left join driver on driver.parish=parish.name

Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.

View 1 Replies View Related

Update Records Matching Multiple Criteria

Feb 13, 2008

I have an 'update' query that looks like this:

update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')

I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?

I guess it would be a case of adding another bracket somewhere but im unsure?

View 3 Replies View Related

Matching Records Using Business Intelligence Studio

Feb 17, 2007

Hello,

I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.

I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.

So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.

TIA,

Kweri

View 1 Replies View Related

Matching Relational Records. Is It Possible Using Data Minig?

Jun 20, 2006

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

View 1 Replies View Related

Update Table_awhere Records Are Not Matching From Table_b

Jan 18, 2008



Hi Gurus!!!
I have two tables tabl_a and tbl_b
now tbl_a has some records which are not in tbl_b. I want to update tbl_b with records in tbl_a
eg:
tbl_a tbl_b
a a
b b
c c
d d
x
y
z
Now I want to update tbl_b with records 'x', 'y', 'z'. I want to keep the matching record just untouched.
Something similar.
How can I do that???
Thanks in advance!!!

View 6 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Data Flow Task To Delete Records And Then Insert Records In Transaction

Aug 6, 2007

HI,

I have been trying to solve the locking problem from past couple of days. Please help mee!!

Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.

The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.

I dont know how to come out of this locking. Please help.

Thanks ,
Sunil

View 7 Replies View Related

SQL Server 2012 :: Pattern Matching (positive Across Records - Negative Within A Field)

Jan 6, 2015

I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:

UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance

I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same

Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082

The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.

Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7

good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.

View 9 Replies View Related

Help With How To Send E-Mail (Parent - Child Matching Records) From SQL 2005 Stored Procedure.

Oct 31, 2007

Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.




Category ID
Category Name
Category E-mail

1
Beverages
Beverages.com

2
Condiments
Condiments.com

3
Confections
 Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.




Category ID
Product Name
Quantity Per Unit

1
Chang
24 - 12 oz bottles

1
Côte de Blaye
12 - 75 cl bottles

1
Ipoh Coffee
16 - 500 g tins

1
Outback Lager
24 - 355 ml bottles

2
Aniseed Syrup
12 - 550 ml bottles

2
Chef Anton's Gumbo Mix
36 boxes

2
Louisiana Hot Spiced Okra
24 - 8 oz jars

2
Northwoods Cranberry Sauce
12 - 12 oz jars

3
Chocolade
10 pkgs.

3
Gumbär Gummibärchen
100 - 250 g bags

3
Maxilaku
24 - 50 g pkgs.

3
Scottish Longbreads
10 boxes x 8 pieces

3
Sir Rodney's Scones
24 pkgs. x 4 pieces

3
Tarte au sucre
48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1),  find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1




Category ID
Product Name
Quantity Per Unit

1
Chang
24 - 12 oz bottles

1
Côte de Blaye
12 - 75 cl bottles

1
Ipoh Coffee
16 - 500 g tins

1
Outback Lager
24 - 355 ml bottlesFor Category Id: 2




Category ID
Product Name
Quantity Per Unit

2
Aniseed Syrup
12 - 550 ml bottles

2
Chef Anton's Gumbo Mix
36 boxes

2
Louisiana Hot Spiced Okra
24 - 8 oz jars

2
Northwoods Cranberry Sauce
12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record?  Any Help or sample URL, Tutorial Page will be appreciated.  Thanks
 

View 3 Replies View Related

Integration Services :: Additional Option In The Lookup (Fail Component On Matching Records)?

Nov 4, 2015

I have an Excel file which contains some data. I want to load that into a SQL server Table. Here are my conditions :

1. If the table doesn't have any matching records from the Excel file, then my DFT should load the data from that Excel to the Dest Table.

2. If the table has even one or more matching records, then the DFT should not process at all, instead I should send an email to the business stating that there are some matching records and hence the package is not process...ed.

P.S. If i use Lookup, I have two matching and non-matching output. which will process the non matching records into the table and matching can be redirected to any flat/Excel file. But i don't want to do this. I just want to lookup the Sql Server table and excel.

It'll be good if there is an additional option in the Lookup "Fail component on matching records".

View 3 Replies View Related

SQL Server 2012 :: CROSS APPLY Returning Records From Left Recordset Even When No Matching Record In Right One

Oct 7, 2014

Following is the query that I'm running:

create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);

select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;

Below is the result for the same:

idname sal
1John500
1ken500
2paulNULL

Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.

One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.

View 6 Replies View Related

Max With Groupby

Apr 8, 2008

i have one query:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0)
order by salestitle


i m getting more than 1000 rows.. in output
some of them are :

salesid salestitle
---------------------------------
11871/20/2005 9:45:54 AM
821622K8_KTBcalendar_pre1 9/19/2007 10:46:02 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
175660AmerHeartMo_2008 2/22/2008 1:05:24 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
25186Cholesterol_Educ_Mo_2006_Rem_test
25282Cholesterol_Educ_Mo_2006_Rem_test


i want (desired)output like: -
11871/20/2005 9:45:54 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test

i want max(salesid) among that same salestitle

when i m doing like:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)

i m getting an error
Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

(0 row(s) affected)

can anybody help me to get correct output.

thanks.

View 9 Replies View Related

Use Of Groupby And Get Max Val

Feb 10, 2007

Respected Friends,
kindly solve my query.

Table Structrue:
Table Name : Custom_Table_SPPR_Master
Fields :
Sno int pk
SPPR_No int pk
Revison_No int pk
rev_Date date
prop_amt decimal
claim_amt decimal
avail_amt decimal
Status int

Data
Sno SPPR_No Revison_No rev_Date prop_amtclaim_amtavail_amtStatus
1 2000 0 2006-10-01 00:00:00.000 1000 600 400 2
2 2000 1 2006-10-01 00:00:00.000 1100 600 500 2
3 2000 2 2006-10-01 00:00:00.000 1400 600 800 2
4 2000 3 2006-10-01 00:00:00.000 1500 900 600 2
5 2001 0 2006-10-01 00:00:00.000 1000 200 800 2
6 2001 1 2006-10-01 00:00:00.000 1200 600 600 2
7 2002 0 2006-10-01 00:00:00.000 1000 600 400 2
8 2002 1 2006-10-01 00:00:00.000 1100 600 500 2
9 2003 0 2006-10-01 00:00:00.000 1000 600 400 2


I want the record where max of the revision no is seen for each sppr
that is the result should the rows of sno: 4,6,8,9


i tried the following but in vein

select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no
-- in this i am getting records, but i want * datas

select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no


select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no)
group by sppr_no,revision_no

Kindly let me the answers . thanks in advance.

View 2 Replies View Related

Using MAX And GROUPBY

Apr 23, 2007

Hi,

I need help in creating a query using MAX and group by.

The input SQL table ClientInfo contains data like the following:

Client Div MaxDt
00003 00 19820501
00003 00 19830208
00003 00 19850801
00003 00 20040811
00003 50 19850101
00003 50 20040811
00003 99 19870131

I need the output to have the max date for every division of every client and output should be as follows:

Client Div MaxDt
00003 00 20040811
00003 50 20040811
00003 99 19870131

Thanks,

View 7 Replies View Related

Groupby

Mar 17, 2008

Hi.I've wrote this query:

SELECT TOP 10 COUNT(*) AS ItemsSold, ProductName, order_details_product_id, SUM(order_details_price) AS order_details_price
FROM tbl_Detail_Order_Product
WHERE (OrderStatus NOT IN (14, 15))
GROUP BY order_details_product_id, ProductName, order_details_price
ORDER BY ItemsSold DESC

which works fine and have these results:
Purchased     Returned    Sold     Part Number    
$ 598.29                         37     16684    
$ 200.29                         33     16654    
$ 300.29                         25     16184    
.
.
.
my problem is I want to show the returned column wich is:

WHERE (OrderStatus IN (14, 15))
I dont know how can I do that.
I tried to write another query but it didnt worked correctly.
plz help me.
thanks

View 1 Replies View Related

Delete Records ?

Jan 31, 2007

Hello, its hard to explain, i have a table like this: --userpage_visitors-- id bigint owner nvarchar(20) visitor nvarchar(20) created datetime
Then i have some code like this:  (@Visitor is send to the stored proc)DECLARE @lastuser nvarchar(20)SELECT TOP 1 @lastuser = visitor FROM userpage_visitors WHERE (owner = @UserName) ORDER BY created DESCIF (@lastuser <> @Visitor)BEGININSERT INTO userpage_visitors (owner, visitor, created) VALUES (@UserName, @Visitor, @Created)-- delete hereEND
Now after i have inserted the new visitor into the table, i need to clean the table... so each user should have maximum of 30 visitors, so if the user i inserted above is the 31st user then i need to delete the first user, so i always have 30 fresch visitors,, if they have less then 30 visitors then nothing should happen. The question is, how can i get the 31th post? in mysql you can say that you want post 30, 31, but in mssql you only have the TOP to select limited posts, any ideas?
Patrick

View 1 Replies View Related

DELETE Records.

Mar 21, 2007

Hello, I have 3 tables with their columns as follows:   + LabelsInDocs [LabelId] PK FK , [DocsId] PK FK   + Labels [LabelId] PK , [LabelName]   + Docs [DocId] PK , [DocUrl] I set Cascade Delete On so when I delete a Doc all records in LabelsInDocs will be deleted. However, when a Doc is deleted I want also to delete all records in Labels for the labels which do not have any Doc associated to it in LabelsInDocs. How can I do this? Thanks, Miguel

View 1 Replies View Related

Delete Records ???

Jun 3, 2008

hello friends.
i have table1 in aspnetdb.mdf and i have picturefile of columname.. datatype of picturefile is vchar(50)
i want to delete my record automatically from my table1 after two weeks from inserting my record date..
i want to delete my picture file that located /pictures/tree.jpg (example).. pictures/tree.jpg was uploaded as picturefile on table1 by user before
how can i do this ?
cheers

View 5 Replies View Related

Pls Help!!! Delete Records In VB.net

Nov 29, 2003

Currently i writing a program to delete Records in the SQL database using VB.net language...
BUT i not sure whether i am right???
Pls provide me with the coding using a command object to delete records in SQL database...thank...however i tried the codes below but not working ...Pls help

Dim StrConnection As String = "workstation id=""ET-T15404-PC1"";integrated security=SSPI; etc

Dim objConnection As New SqlConnection(strConnection)
Dim strSQL As String = "Delete Seller.Admin FROMSeller WHERE Seller.no=tb.no"
Dim dbComm As New SqlCommand(strSQL, objConnection)

objConnection.Open()
dbComm.ExecuteNonQuery
objConnecion.Close()

View 1 Replies View Related

Delete Records

Jun 29, 2005

Hi.

there are 300 records on the table.
the table has a field of "ID (autonumber)"

How can I delete records without latest 20 records?

View 2 Replies View Related

Delete Records

Oct 9, 2006

There are 30 tables in my database and I need to delete all records within a date range. Could anyone help me?

View 4 Replies View Related

Delete Records

Sep 20, 2005

Hello,I have a database called articlesI want to delete all articles from a subgroup e.g. DVDIf I am in the enterprisemanager what steps do I have to take to do thisthanks in advancePaul

View 5 Replies View Related







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